I see you have been going back and forth for days on this issue. You REALLY should have gotten in a professional consultant initially. This type of problem is...

Seems you are new here - so welcome! But please always check the dates on threads you are going to post to. Makes no sense to post...

1) could be parameter sniffing, as Jeff alluded to.

2) could be blocking that is keeping your sproc from running sometimes. use sp_whoisactive to watch for this. amazing...

tfifield (6/23/2011)

---

. . . I always create the index after the SELECT INTO as Kevin suggested, since the create time is better. If the temp table is large, then...

I usually go "kaCHIIINNNGGG" when I see XML and binary or other BLOB data types in a client's schema! 🙂

Anyway, enough of this dead horse. On to other problems!

opc.three (6/23/2011)

---

TheSQLGuru (6/23/2011)

---

I also note that it should be better to put the data into the table and THEN build the index. Should be both faster AND result in...

Jayanth_Kurup (6/23/2011)

---

A rule of thumb , the data decides the datatype , performance and storage considerations are secondary , i.e if the data is fixed length use char else varchar...

tfifield (6/23/2011)

---

TheSQLGuru (6/23/2011)

---

sqldba_icon (6/22/2011)

---

I really appreciate and thank all of you for taking some time to reply to my post. I think i will try creating a temp table...

With the IN clause, the optimizer will use distribution type stats to determine estimated rows and if you have some values that are a large fraction of the total rows...

sujitkmishra (6/15/2011)

---

If you have a fixed length then you should go for char if variable length then varchar. The name of the data types itself is self explanatory.

When comes to...

Don't store date and time in separate fields. What is your reasoning for doing this. Also, I think your query will not return the results you expect with...

sqldba_icon (6/22/2011)

---

I really appreciate and thank all of you for taking some time to reply to my post. I think i will try creating a temp table first, then...

seftest_09 (6/22/2011)

---

Can you show me an example as how to store the result set of an inner query to a temp table and then use it. And why does it...

If you want an amazingly efficient way to collapse date intervals, see here: http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx. Incredible TSQL goodness served up by Itzik Ben-Gan.

3 IN clauses deep with a DISTINCT using LIKE leading percent OR WHERE clause. Nice. 😛

I didn't see any variables here so it is fixed query, right? Perhaps...