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 |
|
NewMedia42
Starting Member
35 Posts |
 Posted - 2004-05-21 : 18:04:12
|
| I have an interesting challenge - I have a particular table which grows by about 400 million rows (TableB) for every 1 million rows (TableA) it's parent table grows. The data contained within is unique to each row, so I breaking it into multiple smaller tables wouldn't particularly help. The bulk of the time it data is being inserted into the table, but once a day I need to perform a series of selects which will ultimately return a sorted list of all the entries - the sort is on TableB.TableAID (So all entries in TableB are grouped together based on their field which contains the unique identifier for TableA).Now, if I add an index to TableB on the TableAID field, the Insert performance is absolutely horrible, probably by a factor of 50x. But, if I don't have that index, then the performance of making select queries is completely unusable more than 10 minutes in some cases for one query, whereas the indexed version does it in closer to 10 seconds).Does anyone have any super-cool suggestions on how to handle this data? I've considered combining all the selects into one select, something like:select * from [TableB] order by [TableAID]And using the querying program to perform the intelligence of breaking it apart.Any thoughts or suggestions on how to handle this? |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-21 : 18:17:55
|
| The parent rows, is there any way to group them by date / week / month / year? If so, you could norizontally partiution the data on that date and you should see some real performance gains.As far as the indexes, maybe you can create the indexes before you run your select, run the select, and then drop the indexes. I'm not sure if that's going to make your select any faster, but it might. If you had the indexes and data on separate RAID arrays, that might help as well. What sort of system does this run on (CPU, RAM, etc)?What sort of disk subsystem does this run on (Disk array, how many arrays, what type, etc)?How big is this database? (This is just because I'm curious.)Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2004-05-21 : 18:39:53
|
| The actual data I'm referencing isn't time/date related, there's basically 1 million unique strings, then about 400 occurances of each unique string (for the 400 million table). So my daily processing is really just dumping out the list of 1 million unique strings, then I also need to build a list of each reference to a unique string from the 400 million table - that's why I need to sort it's returns on the unique identifier from TableA...As far as the system goes, it's a dual Xeon, 8 gig ram, ~3 TB HD (Raid-5, 12 drives) w/ 512mb cache on the Raid card.As far as DB size, I compress most of the large data that goes into it, but it basically is about 100 gigs per 1 million entries. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-21 : 18:54:41
|
| So, are you saying every day you dump basically every row in both tables to disk?? Wow!A few questions:1. How are you performing this Export? BCP? Stored proc to a report generating program? 2. With the export, are you dumping it to a file to be looked at later, or do you have to display that data on-screen ASAP for a user?You've got a 12 drive RAID 5 array? Well, RAID 5 has slow writes and fast reads. 3. How often do these writes occur, and about how many rows per day?If most of the data dosn't change on a daily basis, maybe you can come up with some sort of caching thing where you cache the output from yesterday's dump, and add to it the records that have been inserted between yesterday and today. If this sort of thing will work, then you could add a DateKey as a part of the PK for these two tables, and partition data on that date key. That will make a HUGE difference in performance (looking at thousands of rows or a few million instead of 400 million when doing your selects).Give me a bit more information, and I might be able to help you a bit more.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2004-05-21 : 20:49:11
|
| Yes, I dump those two tables every day, although I would prefer to be able to dump them every eight hours or so. They aren't really dumped though, the database grows, so if another million TableA's were added, it would be a total of 800 million TableB's.As for the actual export, I just do it via ODBC; I suppose I could use BCP or something similar, but due to the size of the resultant data, it becomes pretty unwieldy to introduce another step inbetween.As for the RAID, that's correct - it really screams for reads, and by and large that's what the DB does the most of... It does in the neighborhood of about 2 million inserts when it's running fully, along with those it does about the same number of updates.Aa far as implementing some sort of caching mechanism, the problem is that the 400 references on TableB to TableA all change slightly each day, so while some are the same, a fair number change, are added, or in some cases are removed.Unfortunately, when I do the data build, I do need to include EVERYTHING, so there isn't going to be any tricks I can perform to cull the list in any way - it really comes down to getting the list in the manner I need with the least impact to the server, in the fastest possible way - but that's what we all ultimately wany, eh? :) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-21 : 20:54:07
|
| When you export the data "via ODBC" what is your final output? A big Text file? If so, BCP is going to SMOKE ODBC.There are plenty of BCP experts here, so we can help you make it work if you've not used it before. I just learned BCP and BULK INSERT recently, and I gotta say that stuff is FAST! BULK INSERT is HELLA fast.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-21 : 21:04:20
|
| I agree with what everyone is suggesting here. You need to implement BCP or BULK INSERT for this. It would be far faster. It's actually a very mature and stable tool, so I wouldn't worry about that aspect of it.Also, I don't know if you can do anything about your hardware. Do you by chance have the log files on the same array as the data? Also, you might want to break that RAID array up into two RAID 5 arrays if you can't afford RAID 10. You could place it on two different RAID channels spread across the bus and increase the performance quite a bit. 12 disks is a really big RAID 5 array, and not your most optimal solution.What kind of a controller card/SAN device is that running on?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-21 : 21:09:03
|
| I was thinking the same thing about the TX Logs. They really need to be on a separate RAID 10 array on a channel separate from your big RAID 5 array. If you can afford it, it might be wise to have another RAID 10 array for your indexes as well. That way, you keep all that IO on your Data and Indexes on separate arrays, thus giving you some serious performance gains. If you did that, you could put that index on those tables before you do your export, BCP out the data, and then drop the indexes (to speed up inserts).I think that 12-disk RAID 5 array is killing your performance.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2004-05-22 : 00:54:17
|
| As far as the output of the data - don't worry about that, I'm outputing it into a proprietary format using DeviceIoControl, writing directly to the harddrive sectors, mapping to the topology of the HD for maximum performance - I don't even use NTFS or any file system on the drive. This way when I query the data, I only have an average load time of about ~15ms, and the bulk of that is due to seeks.As far as bulk insert - I can't easily do that because the computers doing the inserts are all running off of compact flash, so I need to minimize the number of writes (due to CF's write limitation), and the actual content size gets big pretty quick...As far as seperating the index onto another physical drive, how can I do that? I can easily resize and remap the raid to put the indexes on another set of drives...As far as logging, I do the minimum and flush it all the time (transactional logs don't matter)... If something catestrophic happens, I can live with losing data.As far as the RAID controller, it's the new 3Ware card - I got it before release but I believe it's available now. It's an awesome card... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-05-22 : 03:21:28
|
quote:
Originally posted by NewMedia42
if I add an index to TableB on the TableAID field, the Insert performance is absolutely horrible ... if I don't have that index, then the performance of making select queries is completely unusable
This is probably a daft off-the-wall thought, but could you INSERT into another table and SELECT from a UNION of the two tables?And then have an overnight routine that [batch] INSERTs from the second table into the first, and then deletes from the second.Kristen |
|
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2004-05-22 : 03:41:24
|
quote:
Originally posted by KristenThis is probably a daft off-the-wall thought, but could you INSERT into another table and SELECT from a UNION of the two tables?And then have an overnight routine that [batch] INSERTs from the second table into the first, and then deletes from the second.
The challenge is that before I insert new items in to TableA, I need to verify they don't already exist - this happens quickly and tends to be lots of inserts in the first hour or so, then after that it's by far more selects - plus I have indexes on this table.I suppose I could place TableB's inserts into a temp database, and then join them in one big pass - but wouldn't this end up with one big session with much greater load on the server than a somewhat consistent load?One more challenging aspect is that the server is basically always in use, so there isn't a window of opportunity where I have a 'night time' that no one accesses it; so from a performance standpoint, a solution that had less impact and took an hour to perform would be more desirable than one which made the server unusable for 5 minutes.You know, the more I describe what the system does, the more I realize what a hassle it is to work on... But it normally doesn't feel that bad! :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-05-22 : 04:45:51
|
| I see a number of Problems, and possible solutions:Issue: Users need to be able to insert quicklyProblem: if there are indexes on the Main Table inserts are slowAnswer: have them insert into a Parallel Table(I have to say that I'm struggling with the fact of Inserts being slowed SO much by presence of indexes that they become unworkable, but. Could this just be revealing a different problem? [Inappropriate FILL of indexes; Lack of Index Rebuild task causing unbalance over time; etc.])Problem: Inserts need to be unique (based on main Table)Answer: Insert using an SProc that first checks both the Main Table and Parallel Table(Assuming indexes on the Main Table then the check is fast [actually, no reason NOT to have indexes on the Parallel Table too, it will only ever contain a modest number of rows])Problem: No big-ish time slot to transfer Parallel Table items to Main TableAnswer: Run the "transfer" routine frequently (Once an hour? Once a minute?)(All we are trying to do here is to save the user slow insert time)Possible problem: All queries have to be rewritten to do UNION ALLAnswer: Rename the existing Main Table; create a view (with the name of the original Main Table) that does a UNION ALL on the Parallel Table(But this may be a performance killer if it causes the optimiser to then make lousy choices)Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-22 : 11:03:48
|
| As far as logging, I do the minimum and flush it all the time (transactional logs don't matter)... If something catestrophic happens, I can live with losing data.You still write every transaction log to the Transaction Logs. It just doesn't keep them there for recovery purposes. When the recovery mode is set to simple, you can just think of your log files as a cache buffer to control transactions and maintain data read/write consistency.As far as seperating the index onto another physical drive, how can I do that? I can easily resize and remap the raid to put the indexes on another set of drives...You would have to recreate the tables and make a new filegroup/file for the indexes. You then create the tables on one filegroup and the indexes on another. The biggest thing is to move the transaction logs off onto another array though. If you can avoid it, you never want to have the data and log files on the same array. They are fundamentally different in how they read/write data and do not interact together optimally.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2004-05-22 : 12:57:41
|
quote:
Originally posted by KristenIssue: Users need to be able to insert quicklyProblem: if there are indexes on the Main Table inserts are slowAnswer: have them insert into a Parallel Table
I can definitely try that for TableB and see how that works...quote:
(I have to say that I'm struggling with the fact of Inserts being slowed SO much by presence of indexes that they become unworkable, but. Could this just be revealing a different problem? [Inappropriate FILL of indexes; Lack of Index Rebuild task causing unbalance over time; etc.])
Got me! :) Actually, I have a rebuild task that runs every 8 hours right now... I think the problem is just the number of inserts. It might also be how I sequence the insert; currently it goes likes this:PRG: while(UniqueValue != NULL) SQL: select ID from [TableA] where [TableA].UniqueValue = UniqueValue PRG: if no ID is returned: SQL: insert into [TableA] (UniqueValue) SQL: select cast(scope_identity() as int) SQL: insert into [TableB] (TableAID, additional data) PRG: UniqueValue = UniqueValue->Next;So this basically shows how the program and SQL interact - I could pretty easily group the TableA queries and the TableB inserts, if there would be any gain to that.quote:
Problem: Inserts need to be unique (based on main Table)Answer: Insert using an SProc that first checks both the Main Table and Parallel Table(Assuming indexes on the Main Table then the check is fast [actually, no reason NOT to have indexes on the Parallel Table too, it will only ever contain a modest number of rows])
The only place inserts are checked against the table is TableA, and it really doesn't have any performance issues - I have a couple of Index's on it, but since it's only in the millions of rows, and it grows non-linearly (in this case slower) as it gets larger, it isn't an issue.TableB is where the problems happen, and I don't need to do any checks before inserting - I can be confident that all of the 400 million or so entries are unique.quote:
Problem: No big-ish time slot to transfer Parallel Table items to Main TableAnswer: Run the "transfer" routine frequently (Once an hour? Once a minute?)(All we are trying to do here is to save the user slow insert time)Possible problem: All queries have to be rewritten to do UNION ALLAnswer: Rename the existing Main Table; create a view (with the name of the original Main Table) that does a UNION ALL on the Parallel Table(But this may be a performance killer if it causes the optimiser to then make lousy choices)
Hmmm... I just don't know if I gain much in this process - since I only effectively perform one query per day on TableB, it would seem faster to just add an index prior to needing to perform the query; then perform the query, then drop the index...quote:
You still write every transaction log to the Transaction Logs. It just doesn't keep them there for recovery purposes. When the recovery mode is set to simple, you can just think of your log files as a cache buffer to control transactions and maintain data read/write consistency
I'll do that at the same time as I move the Index's to a seperate drive as well - I just wasn't that concerned, since the logs are small in relation (I use simple)...quote:
You would have to recreate the tables and make a new filegroup/file for the indexes. You then create the tables on one filegroup and the indexes on another. The biggest thing is to move the transaction logs off onto another array though. If you can avoid it, you never want to have the data and log files on the same array. They are fundamentally different in how they read/write data and do not interact together optimally.
I have a feeling this could be particularly useful to the DB in general - how big do single row index's tend to grow (in relation to the db row)? Can I assume a constant, like 4 bytes for every row, or does it fluctuate?Thanks again for everyone's help on this! I appreciate it! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-22 : 13:00:46
|
| It fluctuates. It depends on the type of index (clustered vs. non-clustered), the fill factor, and what type of data is in the field.BOL has a really good section on how the pages of indexes are constructed and the differences between the types of indexes.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-05-22 : 14:31:56
|
| Ok, I'm in no position to say anything about this except for what you have written here:PRG: while(UniqueValue != NULL)SQL: select ID from [TableA] where [TableA].UniqueValue = UniqueValuePRG: if no ID is returned:SQL: insert into [TableA] (UniqueValue)From your pseudo code here it seems to me that you have an application round-trip for every insert which is a total waste. I might be misunderstanding here but this is what it looks like to me.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2004-05-22 : 17:37:48
|
quote:
Originally posted by LumbagoPRG: while(UniqueValue != NULL)SQL: select ID from [TableA] where [TableA].UniqueValue = UniqueValuePRG: if no ID is returned:SQL: insert into [TableA] (UniqueValue)From your pseudo code here it seems to me that you have an application round-trip for every insert which is a total waste. I might be misunderstanding here but this is what it looks like to me.
If I understand you correctly, you're asking why I don't do the insert test in SQL, correct? If so, the reason is that after it's populated the first million or so records, it is fairly uncommon for an insert to occur - out of 100 queries, only one would result in an insert. The snag is that the information being inserted is usually about 2-4k is size, so I decided to opt on less overhead in the select call and instead require me to handle the absence of the data.I would think it would be more expensive to send a 4k query where most of the time the 4k wasn't used, then it would be to have the additional query - am I wrong in thinking this? I could pretty easily change this if that is the case... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-05-22 : 19:20:12
|
| Hmm, I guess I would have to agree when you put it that way...but to be honest I really don't know. All I know is that server roundtrips usually are quite expensive and that you should avoid them if you can. But if the size of the data is 2-4k I guess I would have done the same... |
|
|
|
|
|
|
|
|
Performance and really BIG tables...