February 13, 2003 at 4:05 pm
Hi,
I am having problem while performing a select query. I have a long comma delimited string (6024 records delimited by comma, to be specific). What i am trying to do is to execute a Select query from my ASP page as follows
Dim myList,sqlStmt,rst,Connection
myList = "1,2,3,4,.................,6024"
sqlStmt = "Select * from tableName where ID in (" & myList & ") "
rst.open sqlStmt,Connection
The problem is that, above statement works fine, but its taking very long (around 5 min) to return results. I am sure I am not posing the SQL query properly. Can any one please help.
thank you in advance,
vijju
February 13, 2003 at 5:47 pm
Because of the IN clause, optimizer cannot use an index on the ID column. If the table is large, it will take awhile to loop through every number in your list. One way around it is to insert these numbers into a temporary table and join two tables on the ID. Then index might be used.
February 18, 2003 at 1:30 pm
Thanks mromm, I was able fasten the query process. Now i am reading and storing each record from the long list into a tempTable. And then using a sub-query to return me the result.
sqlSTMT = " Select * from tableName where ID in ( select ID from tempTable ) "
using this query instead of myList has improved the query processing time. Now I can get the results in 4-5sec instead of 5min. I still do not understand why this difference in performance is?
anyways thanks for your help, really apperciate it.
February 18, 2003 at 1:39 pm
You need to be carefull and make sure you qualify the full name of a column inside a Subquery when the name exists in the outer query.
Like so
select tempTable.ID from tempTable
As there have been seen where the query engine chooses to read subquery colname as outertable.colname.
Also in your example most times this is better to do.
SELECT
*
FROM
tableName tN
LEFT JOIN
tempTable tT
ON
tN.ID = tT.ID
WHERE
tT.ID IS NULL
February 18, 2003 at 3:01 pm
Thank you anteras, for me the inner join worked perfectly.
really appreciate all your comments guys.
April 4, 2003 at 8:56 am
I hate to re-open a seemingly completed issue but I just wanted to get a clarification on this simple script:
SELECT
*
FROM
tableName tN
LEFT JOIN
tempTable tT
ON
tN.ID = tT.ID
WHERE
tT.ID IS NULL
Wouldn't this return the opposite of the desired effect? In my tests I changed this to tT.ID IS NOT NULL to find entries from tableName that have matching IDs in tempTable. When doing the join, shouldn't NULL values appear for the columns in tempTable that DO NOT have matching ID's in the original table? I thought the original script was trying to select from the first table where the ID exists in the second table and this appeared to provide the opposite results. I was just wondering if it was an oversight or if I misunderstood the original request. Either way I'm glad I stumbled across this because this type of temp table use is going to bail me out of an issue I've been working on along these lines of having to search a huge interface database table to find urns that match a previously defined list...
Most of my battles are due to dealing with SQL programming that was created directly from a proprietary application without consideration for redesigning the database or programming structure to take advantage of set-based processing. Not to mention the fact that I'm learning SQL as I go through the process of trying to optimize this application. I can't even tell you how much of a resource the knowledge base of this site has been! Thanks.
April 7, 2003 at 1:11 am
I preffer this:
sqlSTMT = " Select * from tableName where exists ( select * from tempTable where tempTable.Id = tableName.Id ) "
(In old Mssql 6.5 this would be much faster and generic than and IN solution)
quote:
Thanks mromm, I was able fasten the query process. Now i am reading and storing each record from the long list into a tempTable. And then using a sub-query to return me the result.sqlSTMT = " Select * from tableName where ID in ( select ID from tempTable ) "
using this query instead of myList has improved the query processing time. Now I can get the results in 4-5sec instead of 5min. I still do not understand why this difference in performance is?
anyways thanks for your help, really apperciate it.
April 7, 2003 at 4:25 am
In statements with many values tend to be inefficient as any indexes are ignored.
I would sugget building a table of your values, index this column and then use this new table to perform your query on:-
select count(*)
from YourTable
inner join YourValues
on (YourValues.ValueField = YourTable.ValueField)
Even quicker to using an exists statement:-
select count(*)
from YourTable
where exists
(select 'l'
from YourValues
where YourValues.ValueField = YourTable.ValueField)
April 7, 2003 at 4:39 am
quote:
I hate to re-open a seemingly completed issue but I just wanted to get a clarification on this simple script:SELECT
*
FROM
tableName tN
LEFT JOIN
tempTable tT
ON
tN.ID = tT.ID
WHERE
tT.ID IS NULL
Wouldn't this return the opposite of the desired effect? In my tests I changed this to tT.ID IS NOT NULL to find entries from tableName that have matching IDs in tempTable. When doing the join, shouldn't NULL values appear for the columns in tempTable that DO NOT have matching ID's in the original table? I thought the original script was trying to select from the first table where the ID exists in the second table and this appeared to provide the opposite results. I was just wondering if it was an oversight or if I misunderstood the original request. Either way I'm glad I stumbled across this because this type of temp table use is going to bail me out of an issue I've been working on along these lines of having to search a huge interface database table to find urns that match a previously defined list...
Most of my battles are due to dealing with SQL programming that was created directly from a proprietary application without consideration for redesigning the database or programming structure to take advantage of set-based processing. Not to mention the fact that I'm learning SQL as I go through the process of trying to optimize this application. I can't even tell you how much of a resource the knowledge base of this site has been! Thanks.
I believe the actually comment I was referring to with my example has been removed? Or maybe I mixed up with another thread I was looking at but you are right about that example.
Also just a note the IN clause takes full advantage of the index on the ID column in the first statement. NOT IN is the one that does not. That may have been why I made my comment as well as I may have been thinkin along that lines.
April 9, 2003 at 9:53 pm
Totally agree, IN may use indexes (not in 6.5 or earlier), only in a simple case, and NOT IN will not.
But, as a good practice advice : EXISTS and NOT EXISTS, would be a more generic solution.
Considering:
a) IN would work with a simple key (a key of one column). (work as in 'using indexes')
b) But IN is impractical with compose keys( a key of 2 or more cols).
c) EXISTS wouldn't have this problem, neither NOT EXISTS. They may use indexes, if they have a chance.
d) And finally, EXISTS vs a JOIN: the JOIN may multiply the original result set number of rows, the EXISTS will not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply