473,396 Members | 2,158 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

SQL queries that have parameters passed by user

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
6 2101
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
1
by: gary b | last post by:
Hello When I use a PreparedStatement (in jdbc) with the following query: SELECT store_groups_id FROM store_groups WHERE store_groups_id IS NOT NULL AND type = ? ORDER BY group_name
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
7
by: Zlatko Matić | last post by:
Let's assume that we have a database on some SQL server (let it be MS SQL Server) and that we want to execute some parameterized query as a pass.through query. How can we pass parameters to the...
3
by: Zlatko Matić | last post by:
Hello. I'm wondernig what is happennig whith saved pass-through queries nested in regular JET query if regular JET query just filtrates result by start/end date...Does pass-through query first...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
6
by: AdmiralXizor | last post by:
I have written a function that executes action parameter queries given a name and a list of parameters. It splits a string into an array, passes the parameters to the (already saved) query name,...
1
by: JosAH | last post by:
Greetings, Introduction This week we start building Query objects. A query can retrieve portions of text from a Library. I don't want users to build queries by themselves, because users make...
0
MMcCarthy
by: MMcCarthy | last post by:
Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access. Angle brackets <> are used in place of some...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.