Query really slow with eager spool

Question

Query really slow with eager spool

Viewing 5 posts - 16 through 19 (of 19 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Stephane

The scan is scanning the whole index (the whole table in the case of a clustered index) whereas a seek goes straight to where it needs to go to find the data. Therefore a seek is usually desirable. I think the reason for this is that the query optimizer can't use the clustered index to work out the "<= ipTo" part of the "BETWEEN ipFrom and ipTo" clause. Therefore I would recommend you create a non-clustered index on ipTo (assuming that your clustered index is created on ipFrom and ipTo in that order).

John

Stéphane SSC-Addicted Points: 470 More actions · Answer 2

Geez...

Still the same results. Works great for 40-50-60, but not for any other number...

I tried this hint:

update

r

set

countryId = c.countryId

from

#regions r join tbl_ip2countries c WITH (INDEX(PK_tbl_ip2countries))

on

r.ipFloat between c.ipFrom and c.ipTo

Does not improve anything...

Thanks for your time guys!

Stephane

Stéphane SSC-Addicted Points: 470 More actions · Answer 3

I made few more tests and It's working great with few hundreds ip. It's really fast.

I'll stick with that from now on!

Thanks a lot!

Stephane

prabhu.st SSCrazy Points: 2552 More actions · Answer 4

Hi,

Here is where the problem:

update #regions set countryId = (select countryId from tbl_ip2countries where ipFloat between ipFrom and ipTo)

The above update statment will be executed on (roughly 2000 rows as a sample) all the rows of the temp table whereas it reads the data from the base table which is about 2 million records, hence it would read the records from the base table on each row of the temp table, thus it will use the tempdb for a better performace, the Index spool is happening, why don't you try this with a inner join, so it can search for exact rows and make it faster..

Thanks,

Prabhu

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

This thread is six years old.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden