Best practices are really the best (fastest) solution?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » Administering » Best practices are really the best (fastest)...

Add to briefcase

22 posts, Page 1 of 3

Best practices are really the best (fastest) solution?

Expand / Collapse

Author

Message

Posted Monday, September 20, 2010 11:53 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:49 PM
Points: 94, Visits: 426

Hello all,
I was "optimizing" a procedure that was taking a long time to run, and I am really confused about the results,
That procedures did theses things
part a)
select into #temptable from table 1 join table 2 join table 3 ( table 2 and 3 weren't really needed)
( about 50 million records)
create indexes
part b)
select into #temptable2 from ...
create indexes
part c)
select into #temptable3 from ...
create indexes

part d)
select into #temptable4 from #temptable1 join #temptable2 join #temptable3
(about 5 million records)

Resultant time 50 minutes

I changed it to something like this
Part a)
create table #temptable1
create clustered index
insert into #temptable1 select from table1 ( all needed data was here)
create non clustered indexes

Part b)
create table #temptable2
create clustered index
insert into #temptable2 select from ...
create non clustered indexes

Part c)
create table #temptable3
create clustered index
insert into #temptable3 select from ...
create non clustered indexes

Partd d)
select into #temptable4 from #temptable1 join #temptable2 join #temptable3

Resultant time 4 hours

Somebody can explain to me why such a thing is possible? where can I find the very best practices for tuning?
I believed that I was following the rules and look at what happened!

Thanks in advance

Post #989630

Posted Monday, September 20, 2010 12:03 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 6,655, Visits: 5,678

Your question isn't entirely easy to answer. You're missing a few pieces. Like, were you creating clustered indexes in the first sequence, or non-clustered indexes? Secondly, did you change any of the other Select queries like you did the first one?

Things like that will change performance. None of us can give you a decent answer to your problem with the pseudo-code you've listed. However... I can tell you this. Clustered indexes suck for data insertion. They're great for reads, not so much for writes. And 50 million rows? That's a LOT of data. I'm betting your second attempt spent most of that 4 hours just on part a.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #989645

Posted Monday, September 20, 2010 12:07 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:49 PM
Points: 94, Visits: 426

Thanks for the quick response
I didn't change the indexes, only the order
I dind't change the other querys since they already use the correspondant indexes, I only changed the first one since it used 3 tables and all the data was on the fist one and reacheble with a clustered index

Post #989650

Posted Monday, September 20, 2010 12:10 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 6,655, Visits: 5,678

Okay, the best way to figure this out is to try Part A of both methods and time them. I'm betting this is where the problem is. Because you're creating the clustered index prior to data load, it's forcing the data to get reordered as it's inserting.

But that's a guess. As I said, the best way to know for sure is to ignore the other parts, and just do Part A of both methods in two separate SSMS windows. Time it, have it Display Execution Plan, and look at the plan for both bits when it's done running.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #989653

Posted Monday, September 20, 2010 12:11 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 1,117, Visits: 1,135

I think that best practice is to create indexes after the load.
Otherwise you are doing index maintenance for each insert operation which I would think would be substantially slower.

-Dan B

Post #989654

Posted Monday, September 20, 2010 12:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 1,117, Visits: 1,135

Oops! Ditto what Brandie wrote...

Post #989657

Posted Monday, September 20, 2010 12:20 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:49 PM
Points: 94, Visits: 426

Already did that test
Part A, original sproc 14 minutes/New sproc 15 minutes

Post #989668

Posted Monday, September 20, 2010 12:32 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 6,655, Visits: 5,678

Then keep doing the tests one at a time until you find out which part of your method changed so much that it's taking 4 hours to do.

Brandie Tarvin, MCITP Database Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #989684

Posted Monday, September 20, 2010 6:34 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

My first question is, what are you doing with the data that you need all the temp tables? That's probably a lot of spill to disk that might be avoided by avoiding temp tables all together.

Why can't you just do your final join on the base tables that are filling the temp tables? That "might" be faster than the loading temp tables with 50million rows to return 5 million.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #989857

Posted Monday, September 20, 2010 9:54 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 PM
Points: 32,905, Visits: 26,786

The second most important part of optimizing is to know how long each part took using the old way and how long it takes using the new way. Otherwise, you're tuning blindly and are likely going to make things very much worse. Smile

Smile

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #989898

« Prev Topic | Next Topic »

Add to briefcase

22 posts, Page 1 of 3

Permissions

Expand / Collapse