By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,317 Members | 2,385 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,317 IT Pros & Developers. It's quick & easy.

SQL queries that have parameters passed by user

P: n/a
I have an sql query that has specific criteria (like state='PA' or
state = 'NJ'...) and would like to be able to have the user specify
the criteria dynamically either through the web or from MSAccess or
another tool.

The query also does a GROUP BY the state and other variables that are
part of the criteria.

I know how to get MSAccess and asp pages to do the sorting and
selecting against an SQL tbl or view, but when access queries the same
info as the original sql view, the process takes much longer than when
the sql view does all of the sorting, selecting and grouping..

The table we are currently using is 5 million records and will be
growing to 250 million records shortly, so speed is of the essence.

The sql views and MSAccess are both running from the same server so
there is no issue at this point of a network impacting the MSAccess
query.

Any suggestions...
Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Brad (bm***************@bmegroup.com) writes:
I have an sql query that has specific criteria (like state='PA' or
state = 'NJ'...) and would like to be able to have the user specify
the criteria dynamically either through the web or from MSAccess or
another tool.

The query also does a GROUP BY the state and other variables that are
part of the criteria.

I know how to get MSAccess and asp pages to do the sorting and
selecting against an SQL tbl or view, but when access queries the same
info as the original sql view, the process takes much longer than when
the sql view does all of the sorting, selecting and grouping..

The table we are currently using is 5 million records and will be
growing to 250 million records shortly, so speed is of the essence.

The sql views and MSAccess are both running from the same server so
there is no issue at this point of a network impacting the MSAccess
query.

Any suggestions...


I'm afraid that there was very little useful substance in your desciprion.
Which database engine are you using? The only one you mention, but somehow
I suspect that you don't plan to have a 250-million row table in Access.

Assuming that you use Access only as a frontend, and MS SQL Server as
the data store, I still can't tell from your description whether problem
is that there is one single query which is running slowly, or if
Access is issuing many queries. If you don't know this yourself, I would
suggest that you use the Profiler to see what SQL Server gets to work
with.

If you want suggestions to improve the query, I would suggest that you
post the query and CREATE TABLE and CREATE INDEX statements for the
table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
Erland, Thanks for your response. I will try to answer your
questions.
I am new to MS SQL and may not be using the most efficient tools.
Up until recently, all my sql work was done through the Enterprise
Manager, but have just started working with the Query Analyzer.

We are using MS SQL 2000 on a Dell server that also has MS Access 2003
installed.
At this point everything we are doing is running on the one machine,
with the goal of opening the reporting aspect up to asp pages or an
Access front end.

We have several tables and views in MS SQL and have run many queries
and reports in MS Access against linked tables in the MS SQL database
through an ODBC Driver.

I have written a view (in MS SQL that appears below) that shows a
sample what we need against our 5 million record db. It groups on
several fields and filters on a criteria that I would like to be user
selectable.

The query works fine in MS SQL and seems to be fairly quick, but I
don't know how to allow people to select the IDNumber dynamically.
(There may be several ID numbers and other fields that will be part of
the ultimate criteria.)

When I use Access, I just create a form and have the query point to
the form control and get the criteria from there. I can do that here
also and have Access get the data from a linked MS SQL table or view,
but once I start trying to group and sort this quantity of data in MS
Access (even though it is only linking to the data in SQL) it slows
down dramatically and I don't know how to have Access link to this
view and dynamically insert the criteria.

I hope this explanation helps and you can provide me a little
guidance.

Thanks,
Brad

====================LISTING FROM QUERY ANALYZER
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER VIEW dbo.qry_report_rec00Grouped
AS
SELECT COUNT(dbo.tblmaster.recNumber) AS RecCount,
SUM(dbo.tblmaster.PmtAmount) AS SumPmtAmt,
dbo.tblmaster.Type,
dbo.tblmaster.State,
dbo.tblmaster.StatCode,
dbo.tblmaster.CTY,
dbo.qry_HCODE_LKUP.HCODE4
FROM dbo.tblmaster LEFT OUTER JOIN
dbo.qry_HCODE_LKUP ON dbo.tblmaster.recNumber =
dbo.qry_HCODE_LKUP.recNumber
WHERE (dbo.tblmaster.IDNumber = '123456')
GROUP BY dbo.tblmaster.StatCode, dbo.tblmaster.Type,
dbo.tblmaster.State,
dbo.tblmaster.CTY, dbo.qry_HCODE_LKUP.HCODE4

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
=====================END OF LISTING

Erland Sommarskog <es****@sommarskog.se> wrote:
Brad (bm***************@bmegroup.com) writes:
I have an sql query that has specific criteria (like state='PA' or
state = 'NJ'...) and would like to be able to have the user specify
the criteria dynamically either through the web or from MSAccess or
another tool.

The query also does a GROUP BY the state and other variables that are
part of the criteria.

I know how to get MSAccess and asp pages to do the sorting and
selecting against an SQL tbl or view, but when access queries the same
info as the original sql view, the process takes much longer than when
the sql view does all of the sorting, selecting and grouping..

The table we are currently using is 5 million records and will be
growing to 250 million records shortly, so speed is of the essence.

The sql views and MSAccess are both running from the same server so
there is no issue at this point of a network impacting the MSAccess
query.

Any suggestions...


I'm afraid that there was very little useful substance in your desciprion.
Which database engine are you using? The only one you mention, but somehow
I suspect that you don't plan to have a 250-million row table in Access.

Assuming that you use Access only as a frontend, and MS SQL Server as
the data store, I still can't tell from your description whether problem
is that there is one single query which is running slowly, or if
Access is issuing many queries. If you don't know this yourself, I would
suggest that you use the Profiler to see what SQL Server gets to work
with.

If you want suggestions to improve the query, I would suggest that you
post the query and CREATE TABLE and CREATE INDEX statements for the
table.


Jul 23 '05 #3

P: n/a
Brad (bm***************@bmegroup.com) writes:
I have written a view (in MS SQL that appears below) that shows a
sample what we need against our 5 million record db. It groups on
several fields and filters on a criteria that I would like to be user
selectable.

The query works fine in MS SQL and seems to be fairly quick, but I
don't know how to allow people to select the IDNumber dynamically.
(There may be several ID numbers and other fields that will be part of
the ultimate criteria.)
Rather than making it a view, you could use a table-valued function
which takes parameters. If you have more complex criterias, you
might be better of with a stored procedure.

As for performance, for this particular query you should have a
clustered or non-clustered index on tblMaster.IDnumber and a
non-clustered index on (recNumber, HCODE4) in qry_HCODE_LKUP.
When I use Access, I just create a form and have the query point to
the form control and get the criteria from there. I can do that here
also and have Access get the data from a linked MS SQL table or view,
but once I start trying to group and sort this quantity of data in MS
Access (even though it is only linking to the data in SQL) it slows
down dramatically and I don't know how to have Access link to this
view and dynamically insert the criteria.


Well, I don't know Access, so I have no idea what is going on. Although,
I can guess there is a server-side cursor somewhere.

If you are to work with a 250-million-rows table to be, you may find
that a tool like Access that generates a lot of code behind your back
is not optimal. For smaller databases, it's possibly useful, because
it gives you an application in small amount of time. With those volumes,
you need full control over the code, so you can tune performance in every
aspect of the application. You might still be able to use Access - but
then you need to understand exactly what happens when you link tables
in Access etc.

You could use the Profiler to see what is going on with regards to
SQL Server communication. But with those data sizes, you can get
problems in Access itself, if gets the idea to get all those
five million rows to the client side.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

P: n/a
Stu
To add to Erland's suggestions, you should also look at Pass-through
queries in Access; basically, they are a server-side execution tool for
access. You can use parameters and pass them to stored procedures, but
you have to do it in code on the Access side. However, it's worth the
effort.

HTH,
Stu

Jul 23 '05 #5

P: n/a
Stu,
Thanks for your response.
The pass-through query is very powerful and it seems that there is a
way to pass it parameters (I haven't figured that out yet) and the
performance was pretty acceptable.

Thansk for the tip.
Brad

"Stu" <st**************@gmail.com> wrote:
To add to Erland's suggestions, you should also look at Pass-through
queries in Access; basically, they are a server-side execution tool for
access. You can use parameters and pass them to stored procedures, but
you have to do it in code on the Access side. However, it's worth the
effort.

HTH,
Stu


Jul 23 '05 #6

P: n/a
Erland,
Thanks for your tip.
I'm researching this method.
This seems to be very powerful and should be callable from the web
also.
I'll get back if I have more questions.

Thanks again.
Brad

Erland Sommarskog <es****@sommarskog.se> wrote:
Brad (bm***************@bmegroup.com) writes:
I have written a view (in MS SQL that appears below) that shows a
sample what we need against our 5 million record db. It groups on
several fields and filters on a criteria that I would like to be user
selectable.

The query works fine in MS SQL and seems to be fairly quick, but I
don't know how to allow people to select the IDNumber dynamically.
(There may be several ID numbers and other fields that will be part of
the ultimate criteria.)


Rather than making it a view, you could use a table-valued function
which takes parameters. If you have more complex criterias, you
might be better of with a stored procedure.

As for performance, for this particular query you should have a
clustered or non-clustered index on tblMaster.IDnumber and a
non-clustered index on (recNumber, HCODE4) in qry_HCODE_LKUP.
When I use Access, I just create a form and have the query point to
the form control and get the criteria from there. I can do that here
also and have Access get the data from a linked MS SQL table or view,
but once I start trying to group and sort this quantity of data in MS
Access (even though it is only linking to the data in SQL) it slows
down dramatically and I don't know how to have Access link to this
view and dynamically insert the criteria.


Well, I don't know Access, so I have no idea what is going on. Although,
I can guess there is a server-side cursor somewhere.

If you are to work with a 250-million-rows table to be, you may find
that a tool like Access that generates a lot of code behind your back
is not optimal. For smaller databases, it's possibly useful, because
it gives you an application in small amount of time. With those volumes,
you need full control over the code, so you can tune performance in every
aspect of the application. You might still be able to use Access - but
then you need to understand exactly what happens when you link tables
in Access etc.

You could use the Profiler to see what is going on with regards to
SQL Server communication. But with those data sizes, you can get
problems in Access itself, if gets the idea to get all those
five million rows to the client side.


Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.