Performance Issue with "IN" clause (1 Viewer)

Hi,

SELECT a.id
, a.name
, a.type
, b.assign
, b.standard
FROM meta b
, vision a
WHERE b.ID = a.ID
AND a.status = 'RELEASED'
AND a.state = 'Y'
AND a.type = 'Template'
AND Default = 'YES'
AND Format = 'Mixed'
AND Layout = 'Narrow'
AND assign IN('a','b');


I'm using this query to fetch the data from server by comparing values from 2-tables.
I'm getting performance perform, can any one suggest any idea.

Instead of a cartesian join, why not use an inner join? You might try the following change:

SELECT a.id
, a.name
, a.type
, b.assign
, b.standard
FROM meta b
INNER JOIN vision a
ON b.ID = a.ID
WHERE a.status = 'RELEASED'
AND a.state = 'Y'
AND a.type = 'Template'
AND Default = 'YES'
AND Format = 'Mixed'
AND Layout = 'Narrow'
AND assign IN('a','b');

Since you have not specified join fields you have a "cartesian product" meaning the number of results from the join is the product of the number of records in each of the two tables.

After the SELECT of the fields the query should continue:

FROM meta AS b INNER JOIN vision AS a
ON a.ID = b.ID
WHERE etc

You are also using string criteria for all the other fields. These would work faster if words like 'Released', 'Narrow' and 'Mixed were represented as integers. If State is Y or N then it should be a boolean field rather than text.

These field should then be indexed for best performance.

Name, Type and Default are all reserved words and should not be used as object names.

I know this might never get a response (it looks like we're two days and counting - but what the gang has suggested is worth reading carefully...) but I just wanted to ask a thing or two.

This is in the SQL Server forum. Is the query being executed in an Access application linked to server data? Or is it a query you're running on the server itself (via Management Studio or passthrough query or in code)?

The query you had is referred to in various ways. Theta, Non-ANSI (my preference) or, potentially as mentioned, cartesian (though that to me would be the case were there no criteria joining them in the WHERE clause).

SQL Server actually resolves queries so joined (in the WHERE clause) very efficiently.
(Not as well as Oracle I believe - where the non-ANSI syntax is actually standard!)

The ANSI join (FROM ON clause) as suggested is more common - in both Jet and SQL Server, but as I mentioned, SQL Server resolves it very well.
However if you're performing this join in an Access query on two linked tables - then, at best, the query request sent to the server is kind of a hybrid join / multiple batch fetch as Access does with such linked table requests.
This will be adding overhead.
If you create a view on the server performing this join and add this view as a linked table in your Access application - you should see a marked improvement. (FWIW I'd go for the ANSI join too - as shown by ByteMyzer.)

Obviously - any columns used in criteria which are commonly or heavily so used are a candidate for an index on the column. (Ones like "state" and "Default" which appear to hold essentially boolean values aren't a good candidate.)


But even so, unless we're talking a lot of rows (millions), then I'd be surprised if SQL Server didn't fairly rip through this (unless it's on a physical server which lacks "oompf").

Cheers.