OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Efficiency Problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Efficiency Problem [message #372494]

Thu, 15 February 2001 09:17

IvyS
Messages: 1
Registered: February 2001

Junior Member

I've written a program in VBA which prompts the user to select certain values
from a list. The user is permitted to choose one or more values. The program
then runs MS Access and uses a SQL query to get items corresponding to the
user's selections. The problem is that when the user selects a lot of different
values the query takes forever to run (on the order of 20 minutes). If I
remove the WHERE statement, or if the user selects only a few items, the
query runs in about a minute and a half, so I think the reason why it's so
slow is that Access has trouble dealing with a lot of values in an IN statement.
When I tried using OR instead, it told me that the query was too large.
Is there a better way to do this?
I'm new to SQL; I just used Access's QBE function to build the query then
copied the SQL code into my application, so please use small words!

Here's the SQL query. The In statement is lines 7-10.

TRANSFORM " " & First(_Genotype.Allele1 & " " & Allele2) AS Alleles
SELECT Main Table.ID Series, Main Table.ID Family, Main Table.[[ID
Individual]], Main Table.ID Lab, Main Table.ID Father, Main Table.[[ID
Mother]], Main Table.Sex, Main Table.Diagnosis 1 FROM Main Table LEFT
JOIN _Genotype ON Main Table.Counter = _Genotype.Counter WHERE ((([[Main
Table]].ID Series) <> "=NO=" And (Main Table.ID Series) In ("A" , "AX"
,"BS" ,"C" ,"CA" ,"CC" ,"CCA" ,"CF" ,"CO" ,"CP" ,"E" ,"H" ,"I" ,"IA" ,"K"
,"KC" ,"M" ,"MS" ,"NC" ,"NCP" ,"NCS" ,"NYS" ,"OB" ,"OS" ,"OSP" ,"P" ,"PD"
,"PEI" ,"Q" ,"R" ,"S" ,"T" ,"TA" ,"TB" ,"TC" ,"TM" ,"TS" ,"TT" ,"VO" ,"X"
,"Y" ,"YY"))) GROUP BY Main Table.ID Series, Main Table.ID Family,
Main Table.ID Individual, Main Table.ID Lab, Main Table.ID Father,
Main Table.ID Mother, Main Table.Sex, Main Table.Diagnosis 1 ORDER
BY Main Table.ID Series, Main Table.ID Family, Main Table.ID Individual
, _Genotype.Locus PIVOT _Genotype.Locus In ("GABRA6");

Thanks!

IVY

Report message to a moderator

Previous Topic:	Returing some thing like a "result set" from a Stored Procedure
Next Topic:	How to shuffle/scramble records with SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 15 14:35:50 CDT 2024