May 4, 2011 at 9:58 am
I have had TREMENDOUS success at clients (as in 4+ orders of magnitude improvement) using dynamic sql to handle search/paging needs. If you are careful about SQL Injection this design pattern can be amazingly efficient.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 5, 2011 at 1:26 am
With reference to same topic, I created one physical table and trying to inser all the records with page information in it at once. My base table consists of around 8000000 records. The number of records to be inserted in my blank physical table is 2500000. Its taking almost 50 seconds to inser that much amount of data.
There is index seek operation from base table and clustered index on my physical table. Let me know if that can be made some more fast.
May 5, 2011 at 6:11 am
Is around 50 seconds good time to insert around 2500000 records to a physical table A from another table B with 8000000 records ? There are just 8 columns in table A.
May 5, 2011 at 7:12 am
sqlnaive (5/5/2011)
Is around 50 seconds good time to insert around 2500000 records to a physical table A from another table B with 8000000 records ? There are just 8 columns in table A.
I've seen better, but I've seen much worse.
the question is simply is it acceptable for you?
May 5, 2011 at 7:21 am
Definitely not and i'm having problems. Scratched my head a lotz. There I can see index seek in the execution plan. Still its slow. What can be the possibilities to make it fast ?
May 5, 2011 at 7:25 am
sqlnaive (5/5/2011)
Definitely not and i'm having problems. Scratched my head a lotz. There I can see index seek in the execution plan. Still its slow. What can be the possibilities to make it fast ?
Please post the actual execution plan.
May 5, 2011 at 7:27 am
Ninja's_RGR'us (5/5/2011)
sqlnaive (5/5/2011)
Is around 50 seconds good time to insert around 2500000 records to a physical table A from another table B with 8000000 records ? There are just 8 columns in table A.I've seen better, but I've seen much worse.
the question is simply is it acceptable for you?
Index seek for 2.5M rows out of 8M is BAD. You should get a scan to do that. Statistics are whacked, or something else is leading to poor estimated rowcount (table variable, udf maybe?) and index seek plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 6, 2011 at 2:25 am
I'm not using any UDFs or table variable It's just simple insert as below:
Insert into dbo.TableA
PageId, col1, col2, col3, col4, col5, col6, col7, col8)
select
(row_number()OVER (ORDER BY col1, col2, col3) as PageId,
col1, col2, col3, col4, col5, col6, col7, col8
from TableB NOLOCK
where col5 = 'xxx'
and col6 = 'yyy'
May 6, 2011 at 5:16 am
sqlnaive (5/6/2011)
I'm not using any UDFs or table variable It's just simple insert as below:Insert into dbo.TableA
PageId, col1, col2, col3, col4, col5, col6, col7, col8)
select
(row_number()OVER (ORDER BY col1, col2, col3) as PageId,
col1, col2, col3, col4, col5, col6, col7, col8
from TableB NOLOCK
where col5 = 'xxx'
and col6 = 'yyy'
Please post the actual execution plan. Can't help you without that.
May 6, 2011 at 10:32 am
sqlnaive (5/6/2011)
I'm not using any UDFs or table variable It's just simple insert as below:Insert into dbo.TableA
PageId, col1, col2, col3, col4, col5, col6, col7, col8)
select
(row_number()OVER (ORDER BY col1, col2, col3) as PageId,
col1, col2, col3, col4, col5, col6, col7, col8
from TableB NOLOCK
where col5 = 'xxx'
and col6 = 'yyy'
You mentioned index seeks in the plan, but as I said this is BAD for 2.5M out of 8M rows. So try to force a table scan using table hint.
your query is also doing a SORT for the ORDER BY - thus you have now introduced tempdb as a possible perf hit. Do a file IO stall and wait stats analysis while the query is running to see how bad that hit is. Most of my clients have suck-*** tempdb configurations, so I bet this is part of the problem for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 9, 2011 at 7:05 am
Hello SQLGuru, My whole setup was destroyed because of purging. I'll recreate the scenario, check for execution plan and let you know.
May 9, 2011 at 8:29 am
Kindly find the execution plan of the INSERT part of the sproc. It's taking 75% part of query [Sproc takes around 40 seconds to complete]. I've updated the statistics on the table. Let me know how I can reduce this time on Insert.
May 9, 2011 at 8:43 pm
Can anyone please help me out on this ?
May 10, 2011 at 8:00 am
Hello DBA Gurus, Is there something for me ? I've attached the execution plan for the insert as well.
May 10, 2011 at 8:06 am
You think 40 seconds to run this is slow??? Granted it's not blazing but certainly not slow.
That one is out of my expertise zone. Letting the other ones shime in.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply