Slow running Select query

Question

Slow running Select query

Rowan-283474

SSCommitted

Points: 1560
More actions
September 10, 2009 at 5:59 pm

#214375

Hi
I have a two databases on the same SQL 2005 instance.
I created a table, call it 'Table1' on both databases with the same data.
When I look at the execution plan when I run it on 'Database1' it is diffenrent to the execution plan
when I run it on 'Database2'.
The query also runs much longer over 'Database2'
Why is this??
Any help is appreciated.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I suppose indexes are the same and statistics are up to date...

I think some more details are required here to understand what goes on. Please take a look at this article and post some more information. I'll be glad to help you.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

-- Gianluca Sartori

Rowan-283474 SSCommitted Points: 1560 More actions · Answer 2

Hi

I tried to see what the difference was between the two tables and after a while I realised that 'Table1' on 'Database1' was populated through SSIS and 'Table1' on 'Database2' was just populated with an INSERT INTO statement is Management studio.

The data and table definitions are exactly the same BUT the table that is populated through SSIS has a different execution plan and the SELECT query also runs much faster on this table.

I then truncated the table in 'Database2' and populated it with SSIS. After this the exection plan was the same as it is for the table on 'Database1' and the SELECT query also runs much quicker.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

It may simply have to do with the order of the data. If one or the other inserts in the same order as the clustered index, there will be no chance of page splits.

Try rebuilding the indexes on both tables and see if they then take the same time to execute because the source of data makes no difference.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

Does the SSIS package do anything besides insert the data? Maybe the statistics are getting updated as well. If not, maybe they need to be.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning