|
|
Posted by Erland Sommarskog on 09/26/75 11:20
Brad (bmeyynospamnospam@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|