Query slowed to Crawl

Win2k3 IIS Server
MSA 2003

I have an asp page that runs a query. The query would take < 2 seconds to
run from the web page.

Now it takes 52 seconds to run from the web page, but < 2 seconds when run
inside of MSAccess.

SELECT TOP 1 ZIP_CODES.ZIP
FROM ZIP_CODES
ORDER BY Abs([zipval]-Val([nzip]));

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\inetpub\wwwroot\dealer~1\zipbase.mdb;"

Response.Write "Time2: " & timer & " - "
response.write zip & " " newzip = getzip
Response.Write "TimeLast: " & timer
response.write " - " & newzip

Function GetZip()
Response.Write "Time4: " & timer
Set CmdZip = Server.CreateObject("ADODB.Command")
Set ResultZip = Server.CreateObject("ADODB.Recordset")
CmdZip.ActiveConnection = Conn
cmdZip.CommandType = 4
cmdZip.CommandText = "qrySubstituteZip"
cmdZip.parameters.append = cmdZip.CreateParameter("@parm_Zip", 129, 1,
10, zip)
Response.Write "Time5: " & timer
Set resultZip = cmdZip.Execute
Response.Write "Time6: " & timer & " "
if not resultZip.eof then
ret = resultZip.Fields("Zip")
end if
getzip = ret
end function


Zip_Codes is 42000 records long.
Any Ideas?

Thanks,
Todd

Re: Query slowed to Crawl

I would suspect the Order By clause

Try adding a numeric column for the Abs([zipval]-Val([nzip])) value and fill
it in with a batch script. Then index it and use it in the order by clause.

Also, you do not need to explicitly create the recordset in GetZip. The
cmd.execute function will create one.

--
--Mark Schupp


"Todd Heiks" wrote in message
news:OoPI8uPTFHA.2432@TK2MSFTNGP12.phx.gbl...
> Win2k3 IIS Server
> MSA 2003
>
> I have an asp page that runs a query. The query would take < 2 seconds to
> run from the web page.
>
> Now it takes 52 seconds to run from the web page, but < 2 seconds when run
> inside of MSAccess.
>
> SELECT TOP 1 ZIP_CODES.ZIP
> FROM ZIP_CODES
> ORDER BY Abs([zipval]-Val([nzip]));
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=C:\inetpub\wwwroot\dealer~1\zipbase.mdb;"
>
> Response.Write "Time2: " & timer & " - "
> response.write zip & " " newzip = getzip
> Response.Write "TimeLast: " & timer
> response.write " - " & newzip
>
> Function GetZip()
> Response.Write "Time4: " & timer
> Set CmdZip = Server.CreateObject("ADODB.Command")
> Set ResultZip = Server.CreateObject("ADODB.Recordset")
> CmdZip.ActiveConnection = Conn
> cmdZip.CommandType = 4
> cmdZip.CommandText = "qrySubstituteZip"
> cmdZip.parameters.append = cmdZip.CreateParameter("@parm_Zip", 129, 1,
> 10, zip)
> Response.Write "Time5: " & timer
> Set resultZip = cmdZip.Execute
> Response.Write "Time6: " & timer & " "
> if not resultZip.eof then
> ret = resultZip.Fields("Zip")
> end if
> getzip = ret
> end function
>
>
> Zip_Codes is 42000 records long.
> Any Ideas?
>
> Thanks,
> Todd
>

Re: Query slowed to Crawl

Mark-
Thanks for the reply.

That brings it down to about 7 seconds.

Any idea why it would be so much slower in the web page then from Access or
why it broke?

-Todd

"Mark Schupp" wrote in message
news:%23HKD1GQTFHA.2180@TK2MSFTNGP10.phx.gbl...
>I would suspect the Order By clause
>
> Try adding a numeric column for the Abs([zipval]-Val([nzip])) value and
> fill it in with a batch script. Then index it and use it in the order by
> clause.
>
> Also, you do not need to explicitly create the recordset in GetZip. The
> cmd.execute function will create one.
>
> --
> --Mark Schupp
>
>
> "Todd Heiks" wrote in message
> news:OoPI8uPTFHA.2432@TK2MSFTNGP12.phx.gbl...
>> Win2k3 IIS Server
>> MSA 2003
>>
>> I have an asp page that runs a query. The query would take < 2 seconds
>> to run from the web page.
>>
>> Now it takes 52 seconds to run from the web page, but < 2 seconds when
>> run inside of MSAccess.
>>
>> SELECT TOP 1 ZIP_CODES.ZIP
>> FROM ZIP_CODES
>> ORDER BY Abs([zipval]-Val([nzip]));
>>
>> Set Conn = Server.CreateObject("ADODB.Connection")
>> Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
>> Source=C:\inetpub\wwwroot\dealer~1\zipbase.mdb;"
>>
>> Response.Write "Time2: " & timer & " - "
>> response.write zip & " " newzip = getzip
>> Response.Write "TimeLast: " & timer
>> response.write " - " & newzip
>>
>> Function GetZip()
>> Response.Write "Time4: " & timer
>> Set CmdZip = Server.CreateObject("ADODB.Command")
>> Set ResultZip = Server.CreateObject("ADODB.Recordset")
>> CmdZip.ActiveConnection = Conn
>> cmdZip.CommandType = 4
>> cmdZip.CommandText = "qrySubstituteZip"
>> cmdZip.parameters.append = cmdZip.CreateParameter("@parm_Zip", 129, 1,
>> 10, zip)
>> Response.Write "Time5: " & timer
>> Set resultZip = cmdZip.Execute
>> Response.Write "Time6: " & timer & " "
>> if not resultZip.eof then
>> ret = resultZip.Fields("Zip")
>> end if
>> getzip = ret
>> end function
>>
>>
>> Zip_Codes is 42000 records long.
>> Any Ideas?
>>
>> Thanks,
>> Todd
>>
>
>

Re: Query slowed to Crawl

No idea, could the server be bogged down?

If you can explain what you are doing that needs that particular algorithm
perhaps someone could suggest a different approach.

--
--Mark Schupp


"Todd Heiks" wrote in message
news:eCpFojQTFHA.3188@TK2MSFTNGP09.phx.gbl...
> Mark-
> Thanks for the reply.
>
> That brings it down to about 7 seconds.
>
> Any idea why it would be so much slower in the web page then from Access
> or why it broke?
>
> -Todd
>
> "Mark Schupp" wrote in message
> news:%23HKD1GQTFHA.2180@TK2MSFTNGP10.phx.gbl...
>>I would suspect the Order By clause
>>
>> Try adding a numeric column for the Abs([zipval]-Val([nzip])) value and
>> fill it in with a batch script. Then index it and use it in the order by
>> clause.
>>
>> Also, you do not need to explicitly create the recordset in GetZip. The
>> cmd.execute function will create one.
>>
>> --
>> --Mark Schupp
>>
>>
>> "Todd Heiks" wrote in message
>> news:OoPI8uPTFHA.2432@TK2MSFTNGP12.phx.gbl...
>>> Win2k3 IIS Server
>>> MSA 2003
>>>
>>> I have an asp page that runs a query. The query would take < 2 seconds
>>> to run from the web page.
>>>
>>> Now it takes 52 seconds to run from the web page, but < 2 seconds when
>>> run inside of MSAccess.
>>>
>>> SELECT TOP 1 ZIP_CODES.ZIP
>>> FROM ZIP_CODES
>>> ORDER BY Abs([zipval]-Val([nzip]));
>>>
>>> Set Conn = Server.CreateObject("ADODB.Connection")
>>> Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
>>> Source=C:\inetpub\wwwroot\dealer~1\zipbase.mdb;"
>>>
>>> Response.Write "Time2: " & timer & " - "
>>> response.write zip & " " newzip = getzip
>>> Response.Write "TimeLast: " & timer
>>> response.write " - " & newzip
>>>
>>> Function GetZip()
>>> Response.Write "Time4: " & timer
>>> Set CmdZip = Server.CreateObject("ADODB.Command")
>>> Set ResultZip = Server.CreateObject("ADODB.Recordset")
>>> CmdZip.ActiveConnection = Conn
>>> cmdZip.CommandType = 4
>>> cmdZip.CommandText = "qrySubstituteZip"
>>> cmdZip.parameters.append = cmdZip.CreateParameter("@parm_Zip", 129,
>>> 1, 10, zip)
>>> Response.Write "Time5: " & timer
>>> Set resultZip = cmdZip.Execute
>>> Response.Write "Time6: " & timer & " "
>>> if not resultZip.eof then
>>> ret = resultZip.Fields("Zip")
>>> end if
>>> getzip = ret
>>> end function
>>>
>>>
>>> Zip_Codes is 42000 records long.
>>> Any Ideas?
>>>
>>> Thanks,
>>> Todd
>>>
>>
>>
>
>

Re: Query slowed to Crawl

Todd Heiks wrote:
> Win2k3 IIS Server
> MSA 2003
>
> I have an asp page that runs a query. The query would take < 2
> seconds to run from the web page.
>
> Now it takes 52 seconds to run from the web page, but < 2 seconds
> when run inside of MSAccess.
>
> SELECT TOP 1 ZIP_CODES.ZIP
> FROM ZIP_CODES
> ORDER BY Abs([zipval]-Val([nzip]));

It looks like you are trying to get the "closest" match to a zip code
entered by a user, correct?

Has anything changed since the period when it only took 2 seconds for this
query to run? Has the database grown (particularly the zipcode table)? Has
you user base grown? In the latter case, are you minimizing your time
connected to the database by using getstring, getrows arrays or disconnected
recordsets, any of which will alow you to close your connection as soon as
you have the data in your cleintside variable?



>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=C:\inetpub\wwwroot\dealer~1\zipbase.mdb;"
>
> Response.Write "Time2: " & timer & " - "
> response.write zip & " " newzip = getzip
> Response.Write "TimeLast: " & timer
> response.write " - " & newzip
>
> Function GetZip()
> Response.Write "Time4: " & timer
> Set CmdZip = Server.CreateObject("ADODB.Command")
> Set ResultZip = Server.CreateObject("ADODB.Recordset")
> CmdZip.ActiveConnection = Conn

You forgot the Set keyword. By leaving it out, you are telling ADO to take
the default property of Conn (its ConnectionString property) and assign that
property to ActiveConnection. When this is done, a *new* connection is
created and opened behind the scenes when the Command is executed. Use Set
to prevent this from happening:

Set CmdZip.ActiveConnection = Conn

> cmdZip.CommandType = 4
> cmdZip.CommandText = "qrySubstituteZip"
> cmdZip.parameters.append = cmdZip.CreateParameter("@parm_Zip",
> 129, 1, 10, zip)

This won't have an impact on performance, but you don't need an explicit
Command object for this. You can pass your value to the saved query and open
your recordset like this:

Set ResultZip = Server.CreateObject("ADODB.Recordset")
Conn.qrySubstituteZip zip, ResultZip

> Response.Write "Time5: " & timer
> Set resultZip = cmdZip.Execute
> Response.Write "Time6: " & timer & " "
> if not resultZip.eof then
> ret = resultZip.Fields("Zip")
> end if

You failed to close and destroy your recordset here - bad technique which
can impact performance.

I suppose zipcodes is a lookup table?
Instead of having your user enter a zipcode which may or may not be correct,
would you consider "guiding" him to the correct zip, perhaps using a
variation on this dynamic listbox technique:

http://www.thrasherwebdesign.com/downloads1/listdemo.zip

This demo uses client-side vbscript, XML, and the XMLHTTPRequest object to
dynamically refresh a listbox based on characters typed by the user into a
textbox. It consists of 2 pages: the listdemo_client.asp page which is
opened in the browser to display the text and list boxes; and the
listdemo_server.asp page which contains the code needed to retrieve the data
from the Northwind database, convert it to XML, and return it to the client
page. Open the client page, provide the SQL Server connection info, and type
a couple characters into the textbox to watch the listbox be refreshed.

Due to its being written in vbscript, it will only run on IE 5.5 and higher.
It can easily be rewritten in javascript to provide the same functionality
on other browsers.

If you need help with this, follow up in a client-side scripting newsgroup
such as .scripting.vbscript, or, if you need cross-browser functionality,
..scripting.jscript.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Query slowed to Crawl

Maybe someone has played with the configuration of ADO. Take a look at
C:\Program Files\Common Files\System\ADO\MakFre15.bat and ...\MakApt15.bat
and make sure that your ADO objects are still using the Apartment threaded
model; otherwise you will have many kind of issues when running these
objects against a MDB database because Jet doesn't support the free threaded
model reliably.

Don't forget that the wrong choice will also have a big impact on IIS,
forcing it to switch to the STA model and serialize all of your requests.

Take a look at Global.ASA and make sure that you don't create an object of
type STA under Application or Session scope; as this can reduce dramatically
the performance of IIS.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


"Todd Heiks" wrote in message
news:OoPI8uPTFHA.2432@TK2MSFTNGP12.phx.gbl...
> Win2k3 IIS Server
> MSA 2003
>
> I have an asp page that runs a query. The query would take < 2 seconds to
> run from the web page.
>
> Now it takes 52 seconds to run from the web page, but < 2 seconds when run
> inside of MSAccess.
>
> SELECT TOP 1 ZIP_CODES.ZIP
> FROM ZIP_CODES
> ORDER BY Abs([zipval]-Val([nzip]));
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=C:\inetpub\wwwroot\dealer~1\zipbase.mdb;"
>
> Response.Write "Time2: " & timer & " - "
> response.write zip & " " newzip = getzip
> Response.Write "TimeLast: " & timer
> response.write " - " & newzip
>
> Function GetZip()
> Response.Write "Time4: " & timer
> Set CmdZip = Server.CreateObject("ADODB.Command")
> Set ResultZip = Server.CreateObject("ADODB.Recordset")
> CmdZip.ActiveConnection = Conn
> cmdZip.CommandType = 4
> cmdZip.CommandText = "qrySubstituteZip"
> cmdZip.parameters.append = cmdZip.CreateParameter("@parm_Zip", 129, 1,
> 10, zip)
> Response.Write "Time5: " & timer
> Set resultZip = cmdZip.Execute
> Response.Write "Time6: " & timer & " "
> if not resultZip.eof then
> ret = resultZip.Fields("Zip")
> end if
> getzip = ret
> end function
>
>
> Zip_Codes is 42000 records long.
> Any Ideas?
>
> Thanks,
> Todd
>

Re: Query slowed to Crawl

Bob-

Thanks.

I think the 'Set CmdZip' did the trick.

When I came in Monday morning the web query was back to <2 seconds without
me having done anything. Does the memory get released after a time?
The site is not public so it would have been inactive all weekend. I
changed the code as you said and I have not had any issues.

Thanks again,
Todd

"Bob Barrows [MVP]" wrote in message
news:e9tSyzZTFHA.2680@tk2msftngp13.phx.gbl...
> Todd Heiks wrote:
>> Win2k3 IIS Server
>> MSA 2003
>>
>> I have an asp page that runs a query. The query would take < 2
>> seconds to run from the web page.
>>
>> Now it takes 52 seconds to run from the web page, but < 2 seconds
>> when run inside of MSAccess.
>>
>> SELECT TOP 1 ZIP_CODES.ZIP
>> FROM ZIP_CODES
>> ORDER BY Abs([zipval]-Val([nzip]));
>
> It looks like you are trying to get the "closest" match to a zip code
> entered by a user, correct?
>
> Has anything changed since the period when it only took 2 seconds for this
> query to run? Has the database grown (particularly the zipcode table)? Has
> you user base grown? In the latter case, are you minimizing your time
> connected to the database by using getstring, getrows arrays or
> disconnected recordsets, any of which will alow you to close your
> connection as soon as you have the data in your cleintside variable?
>
>
>
>>
>> Set Conn = Server.CreateObject("ADODB.Connection")
>> Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
>> Source=C:\inetpub\wwwroot\dealer~1\zipbase.mdb;"
>>
>> Response.Write "Time2: " & timer & " - "
>> response.write zip & " " newzip = getzip
>> Response.Write "TimeLast: " & timer
>> response.write " - " & newzip
>>
>> Function GetZip()
>> Response.Write "Time4: " & timer
>> Set CmdZip = Server.CreateObject("ADODB.Command")
>> Set ResultZip = Server.CreateObject("ADODB.Recordset")
>> CmdZip.ActiveConnection = Conn
>
> You forgot the Set keyword. By leaving it out, you are telling ADO to take
> the default property of Conn (its ConnectionString property) and assign
> that property to ActiveConnection. When this is done, a *new* connection
> is created and opened behind the scenes when the Command is executed. Use
> Set to prevent this from happening:
>
> Set CmdZip.ActiveConnection = Conn
>
>> cmdZip.CommandType = 4
>> cmdZip.CommandText = "qrySubstituteZip"
>> cmdZip.parameters.append = cmdZip.CreateParameter("@parm_Zip",
>> 129, 1, 10, zip)
>
> This won't have an impact on performance, but you don't need an explicit
> Command object for this. You can pass your value to the saved query and
> open your recordset like this:
>
> Set ResultZip = Server.CreateObject("ADODB.Recordset")
> Conn.qrySubstituteZip zip, ResultZip
>
>> Response.Write "Time5: " & timer
>> Set resultZip = cmdZip.Execute
>> Response.Write "Time6: " & timer & " "
>> if not resultZip.eof then
>> ret = resultZip.Fields("Zip")
>> end if
>
> You failed to close and destroy your recordset here - bad technique which
> can impact performance.
>
> I suppose zipcodes is a lookup table?
> Instead of having your user enter a zipcode which may or may not be
> correct, would you consider "guiding" him to the correct zip, perhaps
> using a variation on this dynamic listbox technique:
>
> http://www.thrasherwebdesign.com/downloads1/listdemo.zip
>
> This demo uses client-side vbscript, XML, and the XMLHTTPRequest object to
> dynamically refresh a listbox based on characters typed by the user into a
> textbox. It consists of 2 pages: the listdemo_client.asp page which is
> opened in the browser to display the text and list boxes; and the
> listdemo_server.asp page which contains the code needed to retrieve the
> data from the Northwind database, convert it to XML, and return it to the
> client page. Open the client page, provide the SQL Server connection info,
> and type a couple characters into the textbox to watch the listbox be
> refreshed.
>
> Due to its being written in vbscript, it will only run on IE 5.5 and
> higher. It can easily be rewritten in javascript to provide the same
> functionality on other browsers.
>
> If you need help with this, follow up in a client-side scripting newsgroup
> such as .scripting.vbscript, or, if you need cross-browser functionality,
> .scripting.jscript.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>