Temporary table and key...

[davism]

Hi all,

Hopefully somebody can help me with this but basically I have a stored procedure that I'm trying to speed up a little bit. This with SQL Server 2005. Right now, it's running 45 seconds in situation #1. In situation #2, it's running about 44 seconds.

Situation #1:

1) Create temporary table.
2) Populate temporary table by a INSERT INTO with a SELECT
3) Update temporary table (using the temporary table field in the WHERE clause.)
4) Select all from temporary table (i.e. *)

Situation #2:

1) Create temporary table
2) Create non-clustered index on one field in the temporary table.
3) Populate temporary table by a INSERT INTO with a SELECT
4) Invoke a child stored procedure.
4a) Child SP - Performs the UPDATE (using a temporary table indexed field in the WHERE clause.)
4b) Child SP - Selects all from the temporary table (i.e. *)

The query plan on situation #2 is NOT showing the index of the temporary table being used but rather a table scan.

The volume of the query in both cases returns 177,000+ rows.
So, I would expect with that volume and the situation #2, that optimizer would know the index exists due to the child stored procedure. But it's yielding the same as situation #1.

Any body have any ideas; is there something I'm missing?

 

[gmmastros]

Try...

1. Create temporary table
2. Populate temporary table
3. Create index.

If you already have appropriate indexes on the table(s) you are populating from, you may want to order the data as you INSERT INTO. This may speed things up even more.

The query plan on situation #2 is NOT showing the index of the temporary table being used but rather a table scan.

There are various reasons why an index would get ignored. I would first make sure that your query is [google]SQL Server sargable[/google].

If none of this helps, it may be best to post the code so we can take a look at it.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Wow - not too good and somewhat thought that it would not do well. I changed it and ran it and it took 57 seconds. I suspect it would take a little longer because it would have to basically regen that binary search after everything.

I have an ORDER BY on the SELECT in the SELECT INTO.

The query plan is showing the index seek on the others and it is from a product that has the indexes fully baked. May not be right but there is nothing I could do there, if not.

I'm not sure what part of the of the code to even provide...

 

[davism]

Any other thoughts? INDEX HINT?

 

[ca8msm]

I think you need to provide the code from the procedure before anyone can offer any precise advice.

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[gmmastros]

I think I agree with Mark.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

I am not complete sure what that will provide...well as a seasoned software developer I do and I don't. The reason I don't. Is some of the queries you will not know what is a key and what isn't. The inner joins help with some of that but one still doesn't know emphatically. However, I will post that in the AM.

Thanks

 

[fredericofonseca]

then as a seasoned developer you also know you will then need to supply us with the tables indexes, and probably with the explain plans you are getting, along with the number of records in each table involved

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[davism]

Actually, I was thinking that a query plan would be the first iteration as that would give some information identifying keys and possible bottlenecks. (i.e. table scans). Then pursue the code, if warranted.

But as a developer I would first look at the general approach. That I listed on my 2 situations. Many a time, that will solve the issue.

If it doesn't next is the query plan. Which is where I posed the question because the query plan is not identifying the index seek where there is an index.

But I'll see what I can do on providing the code. If I can't or not allowed then I'll just have to give the kudos to you all and walk away. Somewhat disappointed; not because of you all but because I can't provide the info.
:-( We'll see tomorrow though.

 

[davism]

I actually got it worked out better and improved the performance. I shaved about 20 seconds off.

I took out some redundant aspects such as groups and I removed an ORDER BY. I changed the index to a nonclustered index and put added another field to it.

It's now performing the index seek in the query plan.

The funny thing now is that the one without the index takes about the same time as the one with the index. Albeit, the one with the index appears more consistent in the times.