Help - time out while trying to pull records down from SQL 1

[Rexolio]

I have a 3 table relational database. I keep getting a timeout on the script below. I'm sure its horrible and would appreciate someone helping me to neaten up!

SELECT Names.*, Info.*, Categories.* FROM Names, Info, Categories WHERE (Names.EntryDate BETWEEN '4/1/02' AND '4/15/02') AND Names.Updates = 'Yes' AND Names.NameID = Info.NameID AND (86 In (Info.CityID,Info.CityID2,Info.CityID3,Info.Shared)) ORDER BY Info.CancelDate ASC

 

[Tarwn]

Three things off the top of my head:
1)If entryDate is a date field then surround your dates in #'s not ''s, er single-quotes that is, though may depend on what type of db your using
2)just for a prettiness factor, how about "SELECT * FROM" instead of the individual tablename.*'s
3) Are you absolutely sure it is the sql, it's entirely possible that you are getting a recordset and it's actually your page going into a while loop without incrememnting through the recordset.
Try littering your script with Response.Write's, followed by response.Flush's
And put a Response.Buffer = false near the top of the script
-Tarwn
Oh, you wanted an answer that worked?

 

[JuanitaC]

One problem I can see is that you are selecting from 3 tables, but you only join two of them. If there is alot of records in your three tables, you could be getting a very huge result set. You join Names and Info on NameID, but you don't include the Categories table...

SELECT Names.*, Info.*, Categories.* FROM Names, Info, Categories WHERE (Names.EntryDate BETWEEN '4/1/02' AND '4/15/02') AND Names.Updates = 'Yes' AND Names.NameID = Info.NameID AND Categories.CatID = ????.CatID AND (86 In (Info.CityID,Info.CityID2,Info.CityID3,Info.Shared)) ORDER BY Info.CancelDate ASC

Also, what is 86? I don't understand what is going on here:
AND (86 In (Info.CityID,Info.CityID2,Info.CityID3,Info.Shared))

 

[Rexolio]

JuanitaC,

86 is a city code, where I'm wanting to find the record where the city code 86 is found in one of the 4 fields in the table Info: CityID, CityID2, CityID3 and Shared.

 

[JuanitaC]

I have never seen IN used that way. Try something like this instead:

SELECT Names.*, Info.*, Categories.* FROM Names, Info, Categories WHERE
...
AND (Info.CityID=86 OR Info.CityID2=86 OR Info.CityID3=86 OR Info.Shared=86) ORDER BY Info.CancelDate ASC

 

[Rexolio]

Hmm, well, my script is working now using the IN part of the string, but I'll try your way too to see if it processes faster. That brings me to another question, though...

If I'm looking for different criteria from each table and then displaying the records, is it best to move from table to table, searching for the critera in each table and then joining that table with the next table, then repeating, like so...

"...WHERE Names.EntryDate = '---' AND Names.NameID = Info.Name ID AND Info.CityID = 86 AND Info.NameID = Categories.NameID AND Categories.CatID = 24..."

OR, can you just hit all the criteria at first and THEN join the tables,like...

"...WHERE Names.EntryDate = '---' AND Info.CityID = 86 AND Categories.CatID = 24 AND Names.NameID = Info.NameID AND Info.NameID = Categories.NameID..."

Which was is faster and/or takes up less system resources? I've often been curious about this.

Thanks, guys!!!

 

[JuanitaC]

I don't know which way is faster, but I always join my tables first... just so I don't forget to do it. I also use the INNER JOIN syntax rather than the WHERE a.ID = b.ID syntax.

Your query with INNER JOIN would look more like this:
SELECT N.*, I.*, C.*
FROM Names N
INNER JOIN Info I ON N.NameID = I.NameID
INNER JOIN Categories C ON N.NameID = C.NameID
WHERE (N.EntryDate BETWEEN '4/1/02' AND '4/15/02')
AND N.Updates = 'Yes'
AND ...

I also like to use alias names just to make the SQL statements easier to read.

If you are still curious about which way would execute faster, the SQL Server forum might be a better place to ask. If you do post your question over there, make sure to include the database and version you are using.