Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Beginer2012
Starting Member
45 Posts |
 Posted - 2012-05-17 : 17:46:36
|
| Hello,I need your opinion on how to do the following:I'm developing a reporting application that reads data from a SQL Server table.Now, each time a user runs the application has to select a survey ID. Based on that ID, I have a procedures that goes and read the log of the collection system for that survey and bulk loaded it into a table,since the log file is updated in real time. Please note each survey have a different log file.I have one database,many surveys and many users. I can not use the same table name for all users, for all surveys.Any suggestions how can I design my database ?Many users can generate reports to the same survey.Thank you |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-17 : 17:52:46
|
quote:
I'm developing a reporting application that reads data from a SQL Server table.Now, each time a user runs the application has to select a survey ID. Based on that ID, I have a procedures that goes and read the log of the collection system for that survey and bulk loaded it into a table,since the log file is updated in real time.
I'm not all clear about the parts in red. Assuming the data you need is stored in the log files, what do you need SQL Server for? Does it store additional information that helps find the appropriate log files?quote:
Please note each survey have a different log file. I have one database,many surveys and many users. I can not use the same table name for all users, for all surveys.
Why can't you use a single table? Are the surveys completely different in structure? |
 |
|
|
Beginer2012
Starting Member
45 Posts |
Posted - 2012-05-17 : 18:15:04
|
| The log files are raw data(comma delimited), so I bulk load the data into a SQL table. Each survey have its own LOG file.I have many users. Basically once the user is done generating the reports, he need to read the data again from the log file because the log file is updated on secondly bases.Thank youI can not use one single table, because the data are read from a file that updated in real time. Lets say user A generated the reports now, and user B is going to generate in 2 minutes from now...The table for user B need to be loaded with the updated data from the log file for that survey.Another instance two users are generating reports at the same time for the same survey, how could they bulk load data into the same table...locking becomes a factor.... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-17 : 18:33:18
|
One method is to use 2 tables, one for querying, the other for staging. You can continuously import the files on a regular basis, once a minute for example, into the staging table. You can then swap the 2 tables after every import using sp_rename, or if you use different schemas, ALTER SCHEMA...TRANSFER.There's a technique for modifying tables while keeping them online documented here:http://michaeljswart.com/2012/04/modifying-tables-online-part-5-just-one-more-thing/That's part 5 of 5 parts, it uses ALTER SCHEMA...TRANSFER. You can read the other parts to get more ideas. Basically instead of bulk loading files with every request, you're bulk loading them regularly and allowing SQL Server to query them normally.quote:
Another instance two users are generating reports at the same time for the same survey, how could they bulk load data into the same table...locking becomes a factor....
How could they access the same file at the same time? Even if they can open the same file twice, their I/O will get serialized and in effect one will block the other. Using the technique I described above avoids this possibility, at the expense of data being possibly 60 seconds out of date, or less. |
|
|
|
|
|
|
|
|
Design