Query Speed

[Mighty]

I am writing a script in VBScript to pull some data from a Pervasive.SQL database and populate an Excel document with the data.

I developed the queries in PCC first to ensure I got the correct syntax and then copied the query into my script. My script is just using an Pervasive Client ODBC connection to the pervasive DB.

The speed of the query in the script is much much slower that the same query running in PCC. How can I improve the speed of the query from my script??

Mighty

 

[mirtheil]

What version of PSQL are you using?
The query speed between PCC and a VBScript call should be very comparable. How long does it take in the PCC? Through VBScript?
What does your VBScript look like?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

Still on Pervasive SQL 2000i.
PCC query speed is roughly 5 seconds. VBScript query time is close5 to 5 minutes.

This is the query in VBScript:

' Connect to the Max Database
set conn = CreateObject("ADODB.Connection")
conn.Open "DSN=MaxDB"

' Get the total sales for the time period for the current product
sql = "SELECT ""Invoice Detail"".""PRTNUM_32"", ""Part Master"".""PMDES1_01"", ""Part Master"".TYPE_01, "
sql = sql & "SUM(IF(""Invoice Detail"".STYPE_32 = 'CU', 1, -1) * ""Invoice Detail"".INVQTY_32) AS totalQty, "
sql = sql & "SUM(IF(""Invoice Detail"".STYPE_32 = 'CU', 1, -1) * ""Invoice Detail"".INVQTY_32 * ""Invoice Detail"".""PRICE_32"") AS totalValue, "
sql = sql & """Lot Tracking Hist"".""LOTNUM_72"" "
sql = sql & "FROM ""Invoice Master"", ""Invoice Detail"", ""Part Master"", ""Lot Tracking Hist"" "
sql = sql & "WHERE ""Part Master"".COMCDE_01 = '" & trim(rs("COMCDE_01")) & "' AND ""Invoice Detail"".INVDTE_32 "
sql = sql & "BETWEEN '" & dateStart("Max") & "' AND '" & dateEnd("Max") & "' "
sql = sql & "AND (""Lot Tracking Hist"".""TNXCDE_72""='S') AND (""Invoice Master"".""INVCE_31"" = ""Invoice Detail"".""INVCE_32"") "
sql = sql & "AND (""Invoice Detail"".""PRTNUM_32"" = ""Part Master"".""PRTNUM_01"") AND "
sql = sql & "(""Invoice Detail"".""PRTNUM_32"" = ""Lot Tracking Hist"".""PRTNUM_72"") AND "
sql = sql & "(CONCAT(""Invoice Detail"".""ORDNUM_32"", CONCAT(""Invoice Detail"".""LINNUM_32"", ""Invoice Detail"".""DELNUM_32"")) = ""Lot Tracking Hist"".""ORDNUM_72"") "
sql = sql & "AND (""Invoice Detail"".""INVCE_32"" = ""Lot Tracking Hist"".""INVCE_72"") "
sql = sql & "GROUP BY ""Invoice Detail"".PRTNUM_32, ""Part Master"".PMDES1_01, ""Part Master"".TYPE_01, "
sql = sql & """Lot Tracking Hist"".LOTNUM_72 "
sql = sql & "ORDER BY ""Invoice Detail"".PRTNUM_32, ""Lot Tracking Hist"".LOTNUM_72"
set rs1 = conn.Execute(sql, , 1)



I know there is a scalar function (CONCAT) used in the join operation but the exact same query is much faster in PCC.

Any suggestions?

Mighty

 

[mirtheil]

What else is happening in the VBScript? Have you put in timing code around the actual execute of the query?
The PCC in 2000i uses ODBC (in V9, it uses JDBC) so the query should be similar.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[TomKane]

Hi,

There are three differences that I can think of

The first one is that there is a difference between running the query in PCC compared to having to first open the DSN through the ADODB connection. There could be a bit of delay here.

The second one is that PCC (in grid mode) only returns a screenfull of records - when using PCC are you executing your query in grid or text mode?

The next thing could be the way you're opening the recordset. I really only use VB6 with opening recordsets and so am not completely sure if the syntax is exactly the same but you could try the following:

Maybe you could declare the recordset first as in:

set rs1 = CreateObject("ADODB.Recordset")

and then opending the recordset like this:

rs1.Open sql, conn, adOpenForwardOnly, adLockReadOnly

You may need to substitute adOpenForwardOnly for zero (0) and adLocalReadOnly for one (1).

Best of luck,
Tom

 

[mirtheil]

Good points Tom. I was thinking along the same lines. One more thing I noticed in looking a little more at the query was that there are two functions (dateStart, dateEnd) and a reference to another result set (trim(rs("COMCDE_01"))). Generating those values in the VBScript probably take longer than if they were hard coded like the query in the PCC.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com