tips on mimimizing report generation time

[bin/bash]

hello everybody out there. generating report is slow especially when the records are large and there are many tables to which we need to query. i want to know is there anyway we can make these queries faster or optimize the code in anyway so that i can generate my reports on a fly. i am using vb6 with mssql7 and crystalreports 8.5.

[RobDog888]

It depends on your queries. Report generation will execute your queries and populate the report with the results. So the first step is
to optimize your queries. Can you post them?

How are you showing the report? CR Report Viewer control? or straight to the printer?

[bin/bash]

this is the entire func:
Function loadReport()
'On Error GoTo localerr
Dim top
Dim qty As String
Dim amt
Dim costprice As Double
Dim sellprice As Double
top = 600
supress = True
strSQL = "select distinct code,description from productmaster order by description"
DataBaseconnection strSQL
myCount = rstset.RecordCount
For i = 1 To rstset.RecordCount
Load lbl(i)
lbl(i).Caption = i
lbl(i).Move 0, top, 495, 615
lbl(i).Visible = True
Load lbl0(i)
lbl0(i).Caption = rstset!Description
lbl0(i).Tag = rstset!code
lbl0(i).Visible = True
lbl0(i).Move 480, top, 2655, 615
top = top + 616
rstset.MoveNext
Next i
top = 600
For i = 0 To myCount - 1
qty = 0
amt = 0
strSQL = "select * from openingstock where productcode='" & lbl0(i + 1).Tag & "' and date='" & Format(Trim(lblDate), "dd-MMM-yyyy") & "'"
DataBaseconnection strSQL
If rstset.RecordCount = 0 Then
qty = "0.0.0"
amt = 0
Remarks = ""
Else
amt = rstset!costprice
store = rstset!openingstock
Remarks = IIf(IsNull(rstset!Remarks), vbNullString, rstset!Remarks)
qty = Convert2Basic(lbl0(i + 1).Tag, rstset!openingstock)
amt = Val(amt) * Val(qty)
qty = store
End If
Load lbl1(i + 1)
lbl1(i + 1).Caption = qty
lbl1(i + 1).Move 3120, top, 855, 615
lbl1(i + 1).Visible = True
Load lbl2(i + 1)
lbl2(i + 1).Caption = amt
lbl2(i + 1).Move 3960, top, 855, 615
lbl2(i + 1).Visible = True
Load lbl16(i + 1)
lbl16(i + 1).Caption = IIf((Remarks = vbNullString), " ", Remarks)
lbl16(i + 1).Move 14040, top, 1815, 615
lbl16(i + 1).Visible = True

strSQL = "select * from receiptstock where productcode='" & lbl0(i + 1).Tag & "' and date='" & Format(Trim(lblDate), "dd-MMM-yyyy") & "'"
DataBaseconnection strSQL
If rstset.RecordCount = 0 Then
amt = 0
qty = "0.0.0"
Else
amt = 0
store = rstset.RecordCount
ReDim receiptQuantities(rstset.RecordCount - 1)
For j = 0 To rstset.RecordCount - 1
amt = Val(amt) + rstset!costprice
receiptQuantities(j) = rstset!receiptstock
rstset.MoveNext
Next j
amt = amt / store
qty = ""
For j = 0 To store - 1
qty = Val(qty) + Convert2Basic(lbl0(i + 1).Tag, receiptQuantities(j))
Next j
amt = Val(amt) * Val(qty)
CheckIfNumericAndConvert "0.0." & qty, lbl0(i + 1)
qty = str1 & "." & str2 & "." & str3
End If
Load lbl3(i + 1)
lbl3(i + 1).Caption = qty
lbl3(i + 1).Move 4800, top, 855, 615
lbl3(i + 1).Visible = True
Load lbl4(i + 1)
lbl4(i + 1).Caption = amt
lbl4(i + 1).Move 5640, top, 855, 615
lbl4(i + 1).Visible = True

Dim qtyreturned() As String
Dim amtreturned As Double
Dim totqtyreturned As String
Dim retCount
amtreturned = 0
'select cp.qtyreturned,cp.amountreturned from creditnoteproducts cp join creditnotemaster cm on cm.code=cp.creditcode where productcode=0 and date='25-apr-2005'
strSQL = "select cp.qtyreturned,cp.amountreturned from debitnoteproducts cp join debitnotemaster cm on cm.code=cp.debitcode where productcode='" & lbl0(i + 1).Tag & "' and date ='" & Format(lblDate, "dd-MMM-yyyy") & "'"
DataBaseconnection strSQL
If rstset.RecordCount <> 0 Then
ReDim qtyreturned(rstset.RecordCount - 1)
retCount = rstset.RecordCount
For l = 0 To rstset.RecordCount - 1
qtyreturned(l) = rstset!qtyreturned
amtreturned = amtreturned + rstset!amountreturned
Next l
totqtyreturned = 0
For l = 0 To retCount - 1
totqtyreturned = Val(totqtyreturned) + Convert2Basic(lbl0(i + 1).Tag, qtyreturned(l))
Next l
Else
totqtyreturned = 0
amtreturned = "0.00"
End If
CheckIfNumericAndConvert "0.0." & totqtyreturned, lbl0(i + 1)
Load lbl13(i + 1)
lbl13(i + 1).Caption = str1 & "." & str2 & "." & str3
lbl13(i + 1).Move 6480, top, 855, 615
lbl13(i + 1).Visible = True
Load lbl14(i + 1)
lbl14(i + 1).Caption = amtreturned
lbl14(i + 1).Move 7320, top, 855, 615
lbl14(i + 1).Visible = True
totalqty = Convert2Basic(lbl0(i + 1).Tag, lbl1(i + 1)) + Convert2Basic(lbl0(i + 1).Tag, lbl3(i + 1)) - totqtyreturned
totalamt = CDbl((lbl2(i + 1))) + CDbl(lbl4(i + 1)) - amtreturned
If totalqty <> 0 Then
CheckIfNumericAndConvert "0.0." & totalqty, lbl0(i + 1)
totalqty = str1 & "." & str2 & "." & str3
Else
totalqty = "0.0.0"
End If
Load lbl5(i + 1)
lbl5(i + 1).Caption = totalqty
lbl5(i + 1).Move 8160, top, 855, 615
lbl5(i + 1).Visible = True
Load lbl6(i + 1)
lbl6(i + 1).Caption = totalamt
lbl6(i + 1).Move 9000, top, 855, 615
lbl6(i + 1).Visible = True

' strSQL = "select * from transactionproducts where productcode='" & lbl0(i + 1).Tag & "' and date='" & Format(lblDate, "dd-MMM-yyyy") & "'"
strSQL = "select tm.date,tp.* from transactionmaster tm join transactionproducts tp on tm.code=tp.billcode where tm.date='" & Format(lblDate, "dd-MMM-yyyy") & "' and tp.productcode='" & lbl0(i + 1).Tag & "'"
DataBaseconnection strSQL
If rstset.RecordCount = 0 Then
RecordCount = 0
qty = "0.0.0"
amt = 0
Else
qty = 0
amt = 0
ReDim reportQuantities(rstset.RecordCount - 1)
RecordCount = rstset.RecordCount
ReDim myStockCodes(rstset.RecordCount - 1)
ReDim myCostPrices(rstset.RecordCount - 1)
For j = 0 To rstset.RecordCount - 1
reportQuantities(j) = rstset!qty
myStockCodes(j) = rstset!stockcode
rstset.MoveNext
Next j
For j = 0 To RecordCount - 1
strSQL = "select basiccostprice from stockmaster where code='" & myStockCodes(j) & "'"
DataBaseconnection strSQL
myCostPrices(j) = rstset!basiccostprice
Next j
For j = 0 To RecordCount - 1
qty = Val(qty) + Convert2Basic(lbl0(i + 1).Tag, reportQuantities(j))
amt = Val(amt) + myCostPrices(j) * Convert2Basic(lbl0(i + 1).Tag, reportQuantities(j))
Next j
CheckIfNumericAndConvert "0.0." & qty, lbl0(i + 1)
qty = str1 & "." & str2 & "." & str3
End If
Load lbl7(i + 1)
lbl7(i + 1).Caption = qty
lbl7(i + 1).Move 9840, top, 855, 615
lbl7(i + 1).Visible = True
Load lbl8(i + 1)
lbl8(i + 1).Caption = amt
lbl8(i + 1).Move 10680, top, 855, 615
lbl8(i + 1).Visible = True

Dim qnty As String, amountret As Double
strSQL = "select dp.qtyreturned,dp.productcode,dp.amountreturned from debitnoteproducts dp join debitnotemaster dm on dp.debitcode=dm.code and dm.date = '" & Format(lblDate, "dd-MMM-yyyy") & "' and dp.productcode='" & lbl0(i + 1).Tag & "'"
DataBaseconnection strSQL
If rstset.RecordCount = 0 Then
qnty = 0
amountret = "0.00"
Else
qnty = rstset!qtyreturned
amountret = rstset!amountreturned
qnty = Convert2Basic(lbl0(i + 1).Tag, qnty)
End If
totalqty = Convert2Basic(lbl0(i + 1).Tag, lbl1(i + 1)) + Convert2Basic(lbl0(i + 1).Tag, lbl3(i + 1)) - Convert2Basic(lbl0(i + 1).Tag, lbl7(i + 1)) - Val(qnty)
totalamt = CDbl((lbl2(i + 1))) + CDbl(lbl4(i + 1)) - CDbl(lbl8(i + 1)) - amountret
If totalqty <> 0 Then
CheckIfNumericAndConvert "0.0." & totalqty, lbl0(i + 1)
totalqty = str1 & "." & str2 & "." & str3
Else
totalqty = "0.0.0"
End If
Load lbl9(i + 1)
lbl9(i + 1).Caption = totalqty
lbl9(i + 1).Move 12360, top, 855, 615
lbl9(i + 1).Visible = True
Load lbl10(i + 1)
lbl10(i + 1).Caption = totalamt
lbl10(i + 1).Move 13200, top, 855, 615
lbl10(i + 1).Visible = True

strSQL = "select tm.date,tp.* from transactionmaster tm join transactionproducts tp on tm.code=tp.billcode where tm.date='" & Format(lblDate, "dd-MMM-yyyy") & "' and tp.productcode='" & lbl0(i + 1).Tag & "'"
DataBaseconnection strSQL
If rstset.RecordCount = 0 Then
RecordCount = 0
qty = "0.0.0"
amt = 0
Else
qty = 0
amt = 0
ReDim reportQuantities(rstset.RecordCount - 1)
RecordCount = rstset.RecordCount
For j = 0 To rstset.RecordCount - 1
reportQuantities(j) = rstset!qty
amt = Val(amt) + rstset!amount
rstset.MoveNext
Next j
For j = 0 To RecordCount - 1
qty = Val(qty) + Convert2Basic(lbl0(i + 1).Tag, reportQuantities(j))
Next j
CheckIfNumericAndConvert "0.0." & qty, lbl0(i + 1)
qty = str1 & "." & str2 & "." & str3
End If
Load lbl12(i + 1)
lbl12(i + 1).Caption = amt
lbl12(i + 1).Move 11520, top, 855, 615
lbl12(i + 1).Visible = True
strSQL = "select cp.qtyreturned from creditnoteproducts cp join creditnotemaster cm on cm.code=cp.creditcode where cp.productcode='" & lbl0(i + 1).Tag & "' and date='" & Format(lblDate, "dd-MMM-yyyy") & "'"
DataBaseconnection strSQL
If rstset.RecordCount <> 0 Then lbl16(i + 1).Caption = lbl16(i + 1).Caption & "Sales Return were performed and adjusted"
top = top + 616
Next i
supress = False
Exit Function
localerr:
MsgBox "Unexpected Error! Dhukuti Will Resume Operation." & vbCrLf & "But If The Problem Persists, Please Contact Support!" & vbCrLf & "Error Number: " & Err.Number & " " & "Error Description: " & Err.Description, vbCritical
'logEvent "Error!", Me.name & "cmdFirst_Click", Err.Number & Err.Description
Resume Next
End Function

[RobDog888]

It looks like your displaying the report on a VB form because I dont see any reference to Crystal Reports at all?

[bin/bash]

yes thats correct. i first collect all the information in a vb form-save it in a table and then show the data in the table in the crystal reports using this:
Private Sub cmdPrint_Click()
On Error GoTo localerr
strSQL = "select * from dailytransactionreport"
DataBaseconnection strSQL
For i = 1 To myCount
rstset.AddNew
rstset!productcode = lbl0(i).Tag
rstset!productdescription = lbl0(i)
rstset!opqty = lbl1(i).Caption
rstset!opamt = lbl2(i)
rstset!reqty = lbl3(i)
rstset!reamt = lbl4(i)
rstset!purretQty = lbl13(i)
rstset!purretAmt = lbl14(i)
rstset!toqty = lbl5(i)
rstset!toamt = lbl6(i)
rstset!coqty = lbl7(i)
rstset!coamt = lbl8(i)
rstset!stkbalqty = lbl9(i)
rstset!stkbalamt = lbl10(i)
rstset!saamt = lbl12(i)
rstset!Date = lblDate
rstset!Remarks = lbl16(i)
rstset.Update
Next i
With CrystalReport1
.ReportFileName = App.Path & "\DAILYTRANSACTIONREPORT.rpt"
.Connect = "DRIVER=SQL Server;SERVER=jereme ;DATABASE=StockMIS ;USER=sa ;PASSWORD=;"
.DiscardSavedData = True
.RetrieveDataFiles
.ReportSource = 0
.ReportTitle = "STOCK IN STOCK OUT"
.Destination = crptToWindow
.PrintFileType = crptCrystal
.WindowState = crptMaximized
.WindowMaxButton = True
.WindowMinButton = True
.WindowShowPrintSetupBtn = True
.Action = 1
End With
strSQL = "delete from dailytransactionreport"
DataBaseconnection strSQL
Exit Sub
localerr:
MsgBox "Unexpected Error! Dhukuti Will Resume Operation." & vbCrLf & "But If The Problem Persists, Please Contact Support!" & vbCrLf & "Error Number: " & Err.Number & " " & "Error Description: " & Err.Description, vbCritical
'logEvent "Error!", "cmdFirst_Click", Err.Number & Err.Description
Resume Next
End Sub

[RobDog888]

Wow, I dont mean to be offensive or anything but your doing double work. Crystal Reports has a Viewer Control that you can place
on your VB6 form. Its a heck of allot better then using vb6 label arrays and such.

If you look at my example in my signature on VB6/CR example, you will see what I mean. Your populating a recordset and the loading that into
your array of labels. Then you call back to the database to populate your recordset and the load your report. My example populates the recordset
and sets that as the viewer source. The viewer can print or export with no coe. Its allot faster and less code otoo.

[bin/bash]

yes thats a problem which i have understood and worked over it too. i wrote a singe query with multiple joins to get all i need but there's a problem to it. i need to do some calculations before i can actually print the report. as you can see from my earlier posts there's helluva lot i need to do. anyway can you tell me how much efficiency/performance will i gain if i convert that single query to a stored procedure and is there any other way i can decrease the lag time. there are about 689 records right now and it will increase with time.

[Hack]

I have specific report tables set up in my DB. Each time I run my queries and create my recordsets, I delete all of the records in the report table I'm about to use, and then dump my recordset into it. My crystal report is bound to this table, and pop...there is my report.

[RobDog888]

You can also do some of the minor calculations in the report itself.

Also, 689 records is very very small. I have a report that is 22,600 pages and the
longest part is when I export it out to pdf. That takes a couple of minutes.

[bin/bash]

22,600 wow! thats really something! as you have already guessed this is a stock report that i am talking about. and the liberty to the user (actually the demand) is that he can choose any date and see the stock statistics till that date. so i can't delete any records on my main tables. as about the table thats actually used for reporting. i can delete. and i will. i think the calculations is eating up the time and also the queries. well lets' forget about these nuts things. tell me, actually give me some tips about how can i manage my database or the queries and any procedures so that report generation doesn't gives my (actually the user's) any nightmares. if you talk about me i am immune to it since i am the developer only and not the user. please.

[RobDog888]

Ok, how many tables are joined in the query? Can you post a sample of the table schema and your query(ies)?