SQL Query Speed Issue Where is it getting hung up?

[Corinne]

I've written a query that pulls data from 2 tables based on a set of criteria and places it in a temp table. I then need to group this data on several fields and have it show on a report with the grouping. So far everything is fine except when the code gets to a specific place - it hangs on the rptDetail.Show. The temp table that the query makes is only 125 records but it tabke over 5 minutes for the report to show with the data. How do I trouble shoot why this is taking so long?

Here is the code to make the temp table:

SELECT tblEntry.ID, tblEntry.AcctNum, tblEntry.CutOff, tblEntry.Code, tblEntry.EntryDate, tblEntry.LanID, tblEntry.CheckNo, tblEntry.CreditAmt, tblEntry.CreditDate, tblEntry.DebitAmt, tblEntry.DebitDate, tblEntry.AmtDiff, tblEntry.Memo, tblSystemHeaders.ID AS Expr1, tblSystemHeaders.Code AS Expr2, tblSystemHeaders.ExpDet,
tblSystemHeaders.Heading, tblSystemHeaders.lblCheckNo, tblSystemHeaders.lblCreditAmt, tblSystemHeaders.lblCreditDate,
tblSystemHeaders.lblDebitAmt, tblSystemHeaders.lblDebitDate, tblSystemHeaders.lblAmtDiff,
tblSystemHeaders.lblMemo INTO tblReport
FROM tblEntry LEFT OUTER JOIN tblSystemHeaders ON tblEntry.Code = tblSystemHeaders.Code
WHERE (tblEntry.AcctNum = '" & Trim(txtAcctNum) & "') AND
tblEntry.CutOff = '" & Trim(cboCutOff) & "')

cnnREX.Execute strSQLREX


Then the data environment takes over with a cmdGrouping:

SELECT Code, CheckNo, CreditAmt, CreditDate, DebitAmt, DebitDate, AmtDiff, Memo, ExpDet, Heading, lblCheckNo, lblCreditAmt,lblCreditDate, lblDebitAmt, lblDebitDate, lblAmtDiff, lblMemo
FROM tblReport
GROUP BY Code, CheckNo, CreditAmt, CreditDate, DebitAmt,
DebitDate, AmtDiff, Memo, ExpDet, Heading, lblCheckNo,
lblCreditAmt, lblCreditDate, lblDebitAmt, lblDebitDate,
lblAmtDiff, lblMemo
ORDER BY Code

Once that data is grouped then:

If DataEnvironment1.rscmdREXGroup_Grouping.State <> 0 Then DataEnvironment1.rscmdREXGroup_Grouping.Close
DataEnvironment1.rscmdREXGroup_Grouping.Open
rptRexDetail.Sections(1).Controls(&quot;lblAcctNum&quot.Caption = txtAcctNum
rptRexDetail.Sections(1).Controls(&quot;lblCutOff&quot.Caption = cboCutOff
rptRexDetail.Show

As soon as it gets to the rptRexDetail.Show it hangs.
Can anyone give me a better alternative to what I have here? I think that because I needed to put the grouping in that this has become very convoluted and I'm now making more work for myself and the app.

Thanks,
Corinne

 

[Corinne]

Any suggestions?

 

[mwolf00]

Have you tried making so that tblReport only has a few rows in it and then run it? Before handling all the data, try just a few rows.

Do you know, for sure, that the report runs quickly up to the command &quot;rptRexDetail.Show&quot;? -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[Corinne]

mwolf00,

Thanks for responding.

tblReport only has 125 records in it. Do you think that I should make it even smaller? I'm asking that because I thought by using SQL it would be robust enough to handle something like this. I just ran it again & it's hung up at rptRexDetail.Show. Any other suggestions?

Thanks,
Corinne

 

[mwolf00]

I have never worked with recordsets in VB, but I do quite a bit in ASP using vbScript. SQL can definately handle 125 rows. How do you loop through recordsets in VB? Im asp I often find that someone uses a loop like...
DO WHILE NOT objRS.EOF

LOOP

and they forget to put an objRS.MOVENEXT statement in to move through the recordset. This will cause the code to hang until it times out. I have also seen code hang when it tries to make a very long output string variable(in asp some people concatenate HTML strings and use 1 response.write to output it). asp pages have trouble adding 50 chracters to a string that is already 1 million characters long, but I don't think that 125 records can do that. -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[Corinne]

mwolf00,

I don't put the data into a recordset. I put it all in a temp table and then pass that to the report. The code does not time out........ I get the results I'm looking for, it's just a speed issue now. Eventually the report comes up after about 5 min of processing.

Thanks,
Corinne

 

[mwolf00]

Why does your second query need a GROUP BY clause? It doesn't do any aggregate calculations. Can you just use an ORDER BY clause? -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[Corinne]

mwolf00,

It has the group by because I need the report grouped by all of those fields.
Something like this:

Canceled Items
The following items were ..........
Check # Amount Stop Date
1 100.00 1/1/02
2 200.00 2/2/02
3 300.00 3/3/02

The top 3 lines are the ones I need to group on and the next 3 lines of data would be the information in the detail section. I'm just thinking that this shouldn't be this hard. I've looked at my data types ........ taken fields off the report ..... any suggestions?

Thanks,
Corinne

 

[mwolf00]

I'm sorry that I haven't been too much help on this, I haven't worked with reports in VB either. You might want to repost question w/ a new title to see if someone that's worked w/ all of this more can help you. -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[Corinne]

mwolf00,

Thanks for you help so far.

Corinne