| Author |
Topic |
|
isingmodel
Starting Member
6 Posts |
 Posted - 2010-10-19 : 21:17:31
|
| I have an application that I would like to execute the following query a few thousand times a second.IF NOT EXISTS(Select TOP 1 ID FROM table1 where column like 'value1') INSERT INTO table1 (column) values('value1') INSERT INTO table2 (column1,column2,column3) values ((Select TOP 1 ID FROM table1 where column like 'value1'), value2, value3)";essentially I want to make sure that value1 is in table1 and to insert value1 into table1 if not. then I want to insert into table2 once I know that table1 has value1 in it. The problem I am running into is speed. I can only execute this on the order of ~10 times a second. I ran accross a page that suggests that I can wrap all the inserts in a Begin Transaction/commit transaction pair and that by making the inserts atomic I would be able to get the kind of insert rate I want. so far ... no dice. The database is MSSQL server 2008 Rb on a local harddrive. any advice would be appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
isingmodel
Starting Member
6 Posts |
Posted - 2010-10-20 : 07:46:25
|
| This is just running on a laptop for a home project.It is reading and storing some web data. Originally I had the program create a table for every new entry, but changed that to just inserting into table2. The code became MUCH slower at that point, when essentially all I did was change a bunch of CREATE TABLE SQL statements to INSERT statements. On average I grab about 4000 pieces of information at a time, and since that data only takes a few seconds to read, I would like to get to the point where updating the DB only takes a few seconds as well.2GHz, 3GBRAM MS SQL EXPRESS standard installation |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-20 : 07:50:07
|
| Putting them all into a single insert would only work if the cause of the bottleneck is contention on the transaction log drive.You need more hardware than you have for this kind of insert speed. You need faster (and dedicated) log drive(s), probably more memory, more processor cores, likely faster drives for the data file.What's the clustering key for this table?Without identifying the cause of the slow down (hint check the wait type in sys.dm_exec_requests) it's hard to be more specific--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-20 : 07:53:11
|
| If you're grabbing 4000 items at a time, can you do a single insert with all 4000 items, not 4000 single row inserts?--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-20 : 11:58:22
|
quote:
Originally posted by tkizerYou are going to need to step up your hardware quite a bit.
A dedicated server, RAID 1 or 10 dedicated drives for the transaction log for starters.--Gail ShawSQL Server MVP |
|
|
|
isingmodel
Starting Member
6 Posts |
Posted - 2010-10-21 : 14:05:22
|
| OK what about getting the data to insert on the order of minutes? I would actually be OK if I could get to 4000 inserts within a minute |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-21 : 14:19:51
|
quote:
Originally posted by GilaMonster
If you're grabbing 4000 items at a time, can you do a single insert with all 4000 items, not 4000 single row inserts?
If yes, inserting all 4000 in a minute should be easy. Inserting all 4000 one row at a time, not so much.--Gail ShawSQL Server MVP |
|
|
|
isingmodel
Starting Member
6 Posts |
Posted - 2010-10-21 : 20:29:07
|
| I found the botleneck is actually the select subquery that is being performed on a text field. I changed that field to VARCHAR and added an index in MSSQL server management studios, but things are still slow. is there anything I need to do to make sure that the column is actually indexed and getting queried by the index? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-22 : 02:17:46
|
I was just going to say that the SELECT looks like the problem. Quite apart from VARCHAR / TEXT issues it is unlikely to be efficient:(Select TOP 1 ID FROM table1 where column like 'value1') What is the purpose of this? You have no order by, so you are trying to select a single row, at random, where "column like 'value1'"LIKE is very slow (compared to, say, EQUALS). If "value1" does not include any wildcards using LIKE is pointless. If "value1" starts with a wildcard character then the whole table will have to be scanned for matching values (well, I presume that SQL is smart enough to stop once its got the single row that you need, but if there is no match the whole table will be scanned). An index on COLUMN should make a considerable difference if the wildcard is not at the beginning of "value1"There is no need to specify TOP 1 or a column name [ID] in an EXISTS statement, you are reducing the options that SQL Server can use, let SQL se if ANY row can be found, by the most efficient means:IF NOT EXISTS(Select TOP 1 ID * FROM table1 ...)In the second INSERT "(Select TOP 1 ID FROM table1 where column like 'value1')" the same things apply as above, no ORDER BY and the use of LIKE. If you want the ID that you have just inserted then use SCOPE_IDENTITY fro the first insert.IF NOT EXISTS(Select TOP 1 ID FROM table1 where column like 'value1') INSERT INTO table1 (column) values('value1') INSERT INTO table2 (column1,column2,column3) values ((Select TOP 1 ID FROM table1 where column like 'value1'), value2, value3)";Note that the first INSERT only happens if the NOT EXISTS is true (but as you don't have a transaction it would be possible for another process to insert a row with COLUMN like 'value1' between the EXISTS and the INSERT ...)The second INSERT always happens - is that what you want?You might be better off with:INSERT INTO table1 (column) SELECT 'value1'WHERE NOT EXISTS(Select * FROM table1 where column like 'value1') and if you only want the second insert to be made IF the first one is made then put this immedaitely after the first insert:IF @@ROWCOUNT <> 0BEGIN INSERT ... second statement ...END (or store the value or @@ROWCOUNT in a local variable and test that before making the second insert - @@ROWCOUNT is reset when the next statement is performed, so you must store it if you want to use it "a bit later on" |
|
|
|
|
Single transaction to increase insert speed?