Posted by Brad on 07/06/05 14:28

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 <esquel@sommarskog.se> wrote:

>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.
>

[Back to original message]