Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query really slow with eager spool


Query really slow with eager spool

Author
Message
Stéphane
Stéphane
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

Hi,

Thanks for your fast answer!

The ip is converted this way: For example, If the IP address 161.132.13.1, then the IP number is 2709785857.
IP Number, X = 161 x (256*256*256) + 132 x (256*256) + 13 x (256) + 1 = 2709785857.

You can have more info here: http://www.ip2location.com/docs/IP2Location_IP_Country_Region_City_Specification.pdf

So it can have 8 or 9 digits.

Sorry for the 256, it's obviously 255!

Thanks

Stephane


John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7414 Visits: 15114

Stephane

OK, that clears up the IP conversion algorithm. I still don't think that your sample data from tbl_ip2countries and from #regions tally up with each other, but let's not worry about that for now.

Try this:

update r
set countryId = c.countryId
from #regions r join tbl_ip2countries c
on r.ipFloat between c.ipFrom and c.ipTo

Not sure how it would perform, but much better than a cursor, I should think. You could experiment with using >= and <= operators instead of the BETWEEN function to see if that goes any faster.

John


Stéphane
Stéphane
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

hum...

That's really weird: For 10 ip, it takes 7-8 seconds. For 100, it takes about a minute which is slower than my problem at the beginning.

BUT!

If I try with 40 or 50 ip, it executes in a flash: 1 second!

The difference is in uses table spool (lazy spool) in the first case but only clustered index seek in the second case.

What the ...???

By the way, in the prod environment, it's going to be up to 1000 ip.

Stephane


Stéphane
Stéphane
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

I really don't get it...

10-20-30 ip uses the lazy spool so it takes several seconds.

40-50-60 ip uses the clustered index seek so it executes in a flash.

70 and more also uses the lazy spool so it takes more than a minute.

Is there a way to force the use of the index instead of the costly table spool?

By the way, it's a clustered index SCAN with the table spool instead of a clustered index seek. What's the difference?

Thanks

Stephane


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671

SCAN : reads all rows.

SEEK : binary search algorithm to find only the required row.

Could you reindex and / or update the statistics on the table.

If that doesn't help you could always use index and join hints (last resort).


John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7414 Visits: 15114

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
Stéphane
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

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
Stéphane
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

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
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 309
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19016
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search