Query returns quickly on ent manager but slow on web/dll

[Karl Blessing]

I have a web application I've been working on thus far it seemed great, the activeX dll behind the scenes gathers up information, puts together a query (a simple one at that) and when doing a lookup/crosswalk query (like if you have a UniqueID and you have a seperate table that defines what those UId are, you can do a query to line up the ID next to the descript)

in enterprise manager, this seems to come back within 5-10 seconds of executing , especially after the SQl Admin put a index on the specific field I needed, however it still comes back 4 minutes after running the query fromt he ActiveX dll, (the Web application times out before it can come back, and the client application using the same dll, does come back but after 4 minutes of waiting) and this is just waiting for TWO records to come back.

The table is huge however, it's running on SQL Server 6.5 , I do belive they rebuilt the indexes last week, so indexes should not be a problem, the table contains about half a million records.

this is the example of the query

Select DISTINCT empdb.stfips, stfipstb.stname, stfipstb.abbrev from empdb, stfipstb where empdb.stfips=stfipstb.stfips Order by empdb.stfips DESC

empdb being the really large table
and stfipstb being the crosswalk/lookup table containing only 3 columns of about 30 records.

the query above comes back with 2 records in Ent manager (10 seconds) , and in the client (4 minutes), but times out on the web application, and I've been told by my employer that it might be possible, in the way that SQL handles database activity from dlls and web application, than it does direct users, and off-server clients.

anyone have any clue on how to speed this process up?
Any hints would be helpful.

Let me know if you need more info (I was going to post the VB Code that calls it, but this is a SQL Forum, and also the call in the DLL was a very simple Rs.open query, conn, static, optomisticlock, and thats whats taking so long, is just simply opening it) Karl Blessing aka kb244{fastHACK}

 

[netcashin]

From your web application, are you querying from within the application or calling a stored procedure on the database to execute the query. If you are not using a stored procedure, try using one.

 

[Karl Blessing]

I dont have the option of using stored procedures, when the queries are dynamically generated (the dll structures the query) the application is a Report generator, so as you can imagine, making a Store procedure for every possible , lookup, retreval, report generation, etc for any 3 combination out of say 20 tables, and just as many columns, isnt exactly something you can just dynamically do in a store procedure (in my knowledge)

But yes, the query is being executed in an Recordset from within the dll. But even still, why wait 4 minutes for a 2 record, 3 column result. Karl Blessing aka kb244{fastHACK}

 

[Tricky28]

How is your DLL talking to the database? Are you using ADO and if so, is the connection through ODBC or OLE DB - switching to OLE DB can prove to be a lot faster.

Richard Speigal
e-mail: rspeigal@ark-group.com

 

[Karl Blessing]

yes the DLL is the componet that talks to the access and SQL Database.

Access - holds the tables, columns, and constraints, so that the application has pre-loaded the above you can chose from before generating a query.

SQL - One database is where all the data comes from (The one I'm having problems with) which is only touched, when lookups, filters, and actual report generation occurs, another database (Same server) is where saved report can be saved (data is not saved, only the queries, and report setup is saved so they can be dynamically generated and changed later)

I am using ADODB , but not sure whether using ODBC or OLE DB, most likely using ODBC , since we use alot of DSN (but we are passing the IP, Server login, etc for the Data SQL Server, but the saved report database is a DSN) Karl Blessing aka kb244{fastHACK}

 

[Karl Blessing]

I have given OleDB a test run on a web app that I have (WS Dianostic) which usally times out as before, anyways, this is the connection string I put together (ASP code)

ConnString = "Provider=SQLOLEDB; Server=eslmi07; Database=" & Request.Form("Path") & "; User Id=" & Us & "; Password=" & Pw & ";"
Response.Write &quot;<!-- &quot; & vbcrlf & ConnString & vbcrlf & SQ & vbclrf & &quot; -->&quot;
Conn.Open ConnString, Us, Pw
RS.Open SQ, Conn, 3, 1

as you can see i put the SQL Query and the Connectionstring out to a comment which you can see here

<!-- 
Provider=SQLOLEDB; Server=eslmi07; Database=lmi07_almis_prod3_test; User Id=****; Password=****;

Select DISTINCT empdb.stfips, stfipstb.stname, stfipstb.abbrev from empdb, stfipstb where empdb.stfips=stfipstb.stfips Order by empdb.stfips DESC -->

username and password blanked out of course

and this is the error generated after a while

Microsoft OLE DB Provider for SQL Server error '80040e31' 

Timeout expired 

/WSD/DSNNav.asp, line 290

line 290 :

RS.Open SQ, Conn, 3, 1

So OLE DB didnt quite seem to do the trick. Karl Blessing aka kb244{fastHACK}