delay expired problem in query

Question

Post reply

delay expired problem in query

djbuzz

Newbie

Points: 8
More actions
February 5, 2010 at 6:45 am

#218834

Hi
This query does not work in my SQL 2008 Server:
SELECT dbo.ip2Tags.ip, dbo.ip2Tags.MediaId
FROM dbo.ip2Tags LEFT OUTER JOIN
dbo.ip2location ON dbo.ip2Tags.ip BETWEEN dbo.ip2location.ipmin AND dbo.ip2location.ipmax
WHERE (dbo.ip2Tags.MediaId = 'mz-1840')
What is wrong in your opinion.
Thank you for your help

Viewing 15 posts - 1 through 14 (of 14 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Not enough info to help you significantly.

What error are you getting?

What are the expected results?

What are the current results?

What is the table definition?

What data do you have in thsoe tables?

The last 2 questions are best answered by posting scripts that will create the tables, keys, indexes and enough sample data to answer the question.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lynn Pettis

SSC Guru

Points: 442467

February 5, 2010 at 7:08 am

#1114403

Are you getting some kind of error?

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

djbuzz (2/5/2010)
Hi
This query does not work in my SQL 2008 Server:
SELECT dbo.ip2Tags.ip, dbo.ip2Tags.MediaId
FROM dbo.ip2Tags LEFT OUTER JOIN
dbo.ip2location ON dbo.ip2Tags.ip BETWEEN dbo.ip2location.ipmin AND dbo.ip2location.ipmax
WHERE (dbo.ip2Tags.MediaId = 'mz-1840')
What is wrong in your opinion.
Thank you for your help

What's the point of the LEFT JOINed table?

You may as well run this:

SELECT t.ip, t.MediaId

FROM dbo.ip2Tags t

WHERE t.MediaId = 'mz-1840'

You will get the same result, probably faster.

If you are trying to use the location table as a filter, then you probably need to inner join it to the tags table. Having done this, if your results are unexpected, then you should review the join condition.

^{“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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I reformatted your code, made a few changes (you really need to look at removing 3 part naming as , iirc, this is being depreciated in future versions of SQL Server so I encourage using table aliases in your from clauses).

I don't see anything syntactically wrong with the code:

SELECT

i2t.ip,

i2t.MediaId

FROM

dbo.ip2Tags i2t

LEFT OUTER JOIN dbo.ip2location i2l

ON i2t.ip BETWEEN i2l.ipmin AND i2l.ipmax

WHERE

(i2t.MediaId = 'mz-1840')

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

lyn - as chris morris says - looks like he doesn't need the left outer join

MVDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Good catch... but it's only true if we're seeing the whole picture... it's rare that we have the real query here. Quite often we are missing columns in the select and where predicates or even order bys.

Let's see what he has to say about that.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Ninja's_RGR'us (2/5/2010)
Good catch... but it's only true if we're seeing the whole picture... it's rare that we have the real query here. Quite often we are missing columns in the select and where predicates or even order bys.
Let's see what he has to say about that.

I have to agree here, how many times have we found that the OP has only given us a small part of the big picture.

djbuzz Newbie Points: 8 More actions · Answer 8

no no real query.

I think the problem is coming from the ip columns that are varchar.

I tried to query with cast or convert but the broblem remains: "timeout"

I'll try to use bigint columns instead of varchar.

Thx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Lynn Pettis

SSC Guru

Points: 442467

February 5, 2010 at 9:35 am

#1114565

Please post the entire and complete error message.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

How about a simple lock?

How about not enough ram to process all the data?

I can keep on guessing but we need more info!!!

Xml Execution plan would be nice.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Ninja's_RGR'us (2/5/2010)
How about a simple lock?
How about not enough ram to process all the data?
I can keep on guessing but we need more info!!!
Xml Execution plan would be nice.

No, SQL Server 2008, provide the Graphical execution plan (saved as a .sqlplan file, zipped, then uploaded).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Lynn Pettis (2/5/2010)
Ninja's_RGR'us (2/5/2010)
How about a simple lock?
How about not enough ram to process all the data?
I can keep on guessing but we need more info!!!
Xml Execution plan would be nice.
No, SQL Server 2008, provide the Graphical execution plan (saved as a .sqlplan file, zipped, then uploaded).

Cool :hehe:

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 13

Anybody wanna beer? It's friday! My shout. Get yer shoes on. Lynn I'll get you a coke.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

lobbymuncher (2/5/2010)
Anybody wanna beer? It's friday! My shout. Get yer shoes on. Lynn I'll get you a coke.

Actually, I'll take a fresh brewed sweet tea, just not as sweet as they make it in Atlanta. I prefer sugar in my tea, not tea in my sugar. 😉