Too many queries make app VERY SLOW 1

[lespaul]

In this project I'm redesigning I'm having a performance issue. The user enters a case number, then I have (right now) 8 queries (eventually it will be probably closer to 15 queries) which all need the case number as a parameter. All the queries have at least one join (most have 2 some have 3) and it's currently taking 20 - 25 seconds to process what I have (I dread to see how long it will take after I'm done!).

Is there any way that I can run all the queries at once rather than one at a time? Would that help my performance issues?

Thanks for any insight!

Leslie

 

[Zathras]

Do you have indexes created over the tables you are selecting from and do they reference the appropriate columns?

You should be able to use more than two tables at a time in a join.

You should select only the data you need to display. If the user is going to drill down to a lower level of detail, query the detail at that time.

 

[LucieLastic]

hi

How are you setting the parameter for each query? I noticed a huge increase in speed once when I changed all my queries from using ParamByName to building the parameter in to the sql statement, eg sql.add(' where id = '+inttostr(id));

Otherwise look at your queries, they may need optimising. Are you using actual inner/outer joins etc or 'from table1, table2 etc'? if you're using the 2nd one (tables in the from), try putting the largest table first in the order.

Are your tables indexed? What db are you using? You could look at using Views, temp tables or stored procedures if you're not having much luck.

If, at the end, you see no improvement, it may be a simple case of a badly designed database or it's been over normalised. If its the latter, try denormalising some of the tables.

lou

 

[lespaul]

The database is an AS400; the database is VERY BADLY DESIGNED (and there's nothing I can do about it). I am only returning the fields that I need rather than SELECT *. I decided not to build my SQL into the code (which I have in the past), but use the parameters because the queries are long and complex (see below for some examples).

What I have is a Main form where the user enters search criteria (Case Number, or Name, or Name & DOB, or Booking Number). If the user enters a case number then I run qryMain (because I need one piece of information from this query as a param, and this is the only query that does a SELECT *, but there are no joins, and I need almost all the information that is returned). Once I get that additional piece of information I run:
qryArrest
qryCashBonds (if not empty then run qryCashBondTransactions with info from qryCashBonds as param)
qrySuretyBonds (if not empth the run qrySuretyBondTrans with info from qrySuretyBonds as param)
qryCaseNotes
qryFileLocation
qryHearingInfo

etc.

Whether or not any of these queries returns any rows determines if a TTabSheet will be displayed in Form_CaseInformation.

Here are a few sample queries:
qryMain:
SELECT * FROM CMPDEFMF WHERE CASPRE = :CASPRE AND CASNUM = :CASNUM

qryArrestthis is one of the longer & more complex queries)
SELECT CMPARRST."BOOKING#", ARHDAT, ARRTIM, ARROFF, ARROFN,
CMPAGNCY.AGNAME, ARRLOC, BNDSET, BNDPAY, RECVDAT, 'Cash' AS BONDTYPE, 'None' AS POSNAM
FROM CMPARRST
INNER JOIN CMPAGNCY ON CMPARRST.ARRAGC = CMPAGNCY.AGENCY
INNER JOIN ACPCBDMF ON CMPARRST."BOOKING#" = ACPCBDMF."BOOKING#"
WHERE CMPARRST.CASPRE = :CASPREC AND CMPARRST.CASNUM = :CASNUMC
AND DEFSEQ = EFSEQC
UNION
SELECT CMPARRST."BOOKING#", ARHDAT, ARRTIM, ARROFF, ARROFN,
CMPAGNCY.AGNAME, ARRLOC, BNDSET, BNDPAY, TRANDATE AS RECVDAT, 'Surety' AS BONDTYPE, POSNAM
FROM CMPARRST
INNER JOIN CMPAGNCY ON CMPARRST.ARRAGC = CMPAGNCY.AGENCY
INNER JOIN ACPSBDMF ON CMPARRST."BOOKING#" = ACPSBDMF."BOOKING#"
INNER JOIN ACPSBDTF ON ACPSBDMF.BNDNUM = ACPSBDTF.BNDNUM
WHERE CMPARRST.CASPRE = :CASPRES AND CMPARRST.CASNUM = :CASNUMS
AND DEFSEQ = EFSEQS
ORDER BY ARHDAT

qryCashBond: SELECT BNDNUM, RECVDAT, POSNAM, ACPCBDMF."BOOKING#",
BNDAMT, RFNAME
FROM ACPCBDMF
WHERE CASPRE = :CASPRE AND CASNUM = :CASNUM

qryCashTrans:
SELECT BNDNUM, ACPCBDTF.TRNCDE, TRNDSC, TRANDATE, TRNAMT
FROM ACPCBDTF
INNER JOIN ACPCBDTR ON ACPCBDTF.TRNCDE = ACPCBDTR.TRNCDE
WHERE BNDNUM = :BONDNUM

qryFileLocation:SELECT LOCDATE, LOCTIME, LOCDESC, EMPNAM
FROM CMPFILLO
INNER JOIN CMPLOCAT ON CMPFILLO.LOCCODE = CMPLOCAT.LOCCODE
INNER JOIN HRPEMPMF ON CMPFILLO."BADGE#" = HRPEMPMF."BADGE#"
WHERE CASPRE = :CASPRE AND CASNUM = :CASNUM

Thanks for your assistance!

Leslie

 

[lespaul]

Lou, have a star! After looking into writing the queries differently and other off the wall ideas, I decided not to pass the params, but to write the SQL into the code and it runs many time faster (instead of 25 - 30 seconds only taking 5 or less!).

Thanks!

Leslie

 

[lespaul]

Does anyone know what might cause this poor performance when using the ParamByName?

Leslie

 

[LucieLastic]

hi Leslie

I wish I knew why it made such a difference. Thanks for the star and I'm glad you've managed to improve it.

lou