Using Pass throw query in forms

[Roy Goldhammer]

Hello there

On my program there are combo boxes that the rowsource is bound to access
query.

To improve performance I've created store procedures in sql server, crated
pass throw quries that connect to the store procedures.

This works fine, exept that the store prodecures, who are bases on
parameters which is in the form. And in order to use the paramater i have
(as far as i know) to use code at the beginning of the form that do:
1. get the queries from the Currentdb.QueryDefs collection
2. change the sql with the new sql parameters

The code is look like this:
dim qry as queryDef
set qry = currentdb.QueryDefs("qry")
qry.sql = "EXEC sto " & me.parameter

This procedure is very heavy. (it takes at least 3 seconds for every time i
run it)

And to thing that i need it to more then one query it will be catastrophy

Is there better way to use pass throw query in this metter?


--
øåòé âåìãäîø
òúéã äðãñú úåëðä
èì' 03-5611606
ôìà' 050-7709399
àéîééì: (e-mail address removed)

 

[Douglas J Steele]

What you're doing is probably the best approach. I suspect it's running the
SPs that's taking the time, not changing the SQL of the QueryDef.

 

[Roy Goldhammer]

Whell Douclas. I've check it dosent of times, and the using of queryDef is
the thing that takes the most time.

it because on my database there are more then 3000 quries.

How can i use pass throw not by using queryDef?

--
øåòé âåìãäîø
òúéã äðãñú úåëðä
èì' 03-5611606
ôìà' 050-7709399
àéîééì: (e-mail address removed)

 

[Douglas J Steele]

I don't believe you can.

Why are there more than 3000 queries? Are there many duplicate or
near-duplicate queries?

 

[Rick Brandt]

Whell Douclas. I've check it dosent of times, and the using of queryDef is the
thing that takes the most time.

it because on my database there are more then 3000 quries.

How can i use pass throw not by using queryDef?

I seriously doubt that. One of the reasons that Stored Procedures on the server
are not always a performance silver bullet is that when you query against a link
or use a passthrough query you will see results as soon as a few pages of data
have been retrieved and the query will ask for more rows as you access them.
With a Stored Procedure you see nothing until the entire procedure has completed
on all rows and then it is returned to Access all at once.