wipnav
asked on
pass-through query
I am migrating my Access database to SQL Server Express and am not sure the best way to get my data from the server.
I am familiar with writing a pass through query that will return data from a stored procedure on the server but have run into a dilemma. I have a form that has 6 different listboxes, all with (currently) different select statements as their row source. Would I need to have a pass-through query for each listbox for a total of 6 queries?
What about unioning all the results in the stored procedure so a single pass-through query can be run to return the data, then set the row source of each listbox to select only the data from the pass-through query that it needs?
I would like to:
Minimize the # of hits to the server
Write as much SQL code in SQL Management Studio as possible
I am familiar with writing a pass through query that will return data from a stored procedure on the server but have run into a dilemma. I have a form that has 6 different listboxes, all with (currently) different select statements as their row source. Would I need to have a pass-through query for each listbox for a total of 6 queries?
What about unioning all the results in the stored procedure so a single pass-through query can be run to return the data, then set the row source of each listbox to select only the data from the pass-through query that it needs?
I would like to:
Minimize the # of hits to the server
Write as much SQL code in SQL Management Studio as possible
ASKER
They are not cascading queries.
What exactly do you mean that you can retrieve multiple recordsets as part of a single recordset variable? Can you provide an example?
What if I passed the values for each object as a single delimited record then place it in an array and add the array as the rowsource of the listbox?
This is one example I saw: http://gregmaxey.mvps.org/
What exactly do you mean that you can retrieve multiple recordsets as part of a single recordset variable? Can you provide an example?
What if I passed the values for each object as a single delimited record then place it in an array and add the array as the rowsource of the listbox?
This is one example I saw: http://gregmaxey.mvps.org/
ASKER
Is there much of a performance difference between returning 1,000 records in 1 shot (5 union all queries) or submitting 5 seperate queries to the sql server?
Also, if I selected all the data into the single pass-through query then did smaller subqueries as the row source for my listboxes from that query, would each subquery cause the pass-through query to be executed an additional time?
Also, if I selected all the data into the single pass-through query then did smaller subqueries as the row source for my listboxes from that query, would each subquery cause the pass-through query to be executed an additional time?
What fyed is saying is that if you get as many query result sets back as you have Select statements in your Stored Procedure
If your SP has:
...
Select * from tableA;
Select * from tableB;
Select * from tableC;
...
it will return 3 recordsets in the ADO call that reuns the Stored procedure
If your SP has:
...
Select * from tableA;
Select * from tableB;
Select * from tableC;
...
it will return 3 recordsets in the ADO call that reuns the Stored procedure
ASKER
OK. I have just discovered using ADO commands to execute stored procedures (still new to SQL server and other app interactions).
How do I reference each recordset in Access?
How do I reference each recordset in Access?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks for the help!
< :-) >
...it should be noted though that despite all the above wisdom, any Real Programmer would implement exactly that - UNION, sophisticated code spreading the result in different places etc. - indeed, "Real Programmers write self-modifying code, especially if they can save 20 nanoseconds in the middle of a tight loop." ("Real Programmers Don't Use Pascal", Datamation, volume 29 number 7, July 1983 , cab be read at
http://www.pbm.com/~lindahl/real.programmers.html)
</ :-) >
...it should be noted though that despite all the above wisdom, any Real Programmer would implement exactly that - UNION, sophisticated code spreading the result in different places etc. - indeed, "Real Programmers write self-modifying code, especially if they can save 20 nanoseconds in the middle of a tight loop." ("Real Programmers Don't Use Pascal", Datamation, volume 29 number 7, July 1983 , cab be read at
http://www.pbm.com/~lindahl/real.programmers.html)
</ :-) >
Part of the problem is that many "real programmers" will spend lots of time learning new languages, but won't put in the effort to learn proper SQL.
Cannot tell you how many times I have seen relatively simple SQL statements built on the fly and resulting in 50 or 60 lines of code, when they could achieved a much more elegant and faster solution by creating a stored procedure and just passing parameters to the SP.
I'll take a database guy who has learned to code over a "real programmer" any time.
Cannot tell you how many times I have seen relatively simple SQL statements built on the fly and resulting in 50 or 60 lines of code, when they could achieved a much more elegant and faster solution by creating a stored procedure and just passing parameters to the SP.
I'll take a database guy who has learned to code over a "real programmer" any time.
Elegance is subjective. The s.p.-based solution is more elegant for someone who knows SQL well enough. For someone who is much more familiar with programming language, his 50 or 60 lines are more elegant, and more to the point, more maintainable than 10-20 lines of t-sql. Add here practically absent error handling, plus general asceticism of the language, and maybe programming in object-oriented, well structured .Net language, with proper encapsulation of every step in try...catch, is not such a bad idea.
As for the speed, no doubt, what does consume significant resources, needs to be optimized, but if it "saves 20 nanoseconds in the middle of a tight loop", then it's probably less important.
As for the speed, no doubt, what does consume significant resources, needs to be optimized, but if it "saves 20 nanoseconds in the middle of a tight loop", then it's probably less important.
However, if they are cascading, you will not be able to do this, and would need separate queries for each.