Re: 2nd layer of filtering?
- From: Otis Mukinfus <phony@xxxxxxxxxxxxxxxx>
- Date: Mon, 02 Jan 2006 12:51:12 -0600
On Sun, 1 Jan 2006 17:34:03 -0800, "jonefer"
<jonefer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>I have a paramaterized command for a data adapter
>
>SELECT MRN, Name, Sex, DOB, SSN
>FROM dbo.Membership
>WHERE (MRN = @MRN or @MRN is null)
> AND (Name like @Name or @Name is null)
> AND (Sex = @Sex or @Sex is null)
>etc.
>The name of that result set is from a stored procedure called:
>qMembershipSelect
>
>for 1.4 million records, this ensures that the data will come up quickly
>because all I'm doing is filtering between 1-4 parameters.
>
>But after the first select is done, I'd like to apply additional filtering
>to that result set
>that weeds out other stuff like duplicate records and selects a true Current
>member:
>
>SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
>[FROM-DT], [THRU-DT]
>FROM qMembershipSelect AS Y
>GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
>HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
>qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
>>From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
>ORDER BY MemNAME, [FROM-DT] DESC;
>
>Obviously this 2nd layer of filtering wouldn't be so tedious to a smaller
>dataset as it would to 1.4 million records at the start.
>
[snip]
The syntax for doing this in C# is like the example below:
DataRow [] cities =
ds.USCities.select("city = 'arlington'", "city, state ASC");
For an explanation of what the code is doing check the VS DOCs.
However you cannot use DISTINCT.
I have to ask; Why are you pulling 1.4 million rows in the initial
query? Even if you cut the number of columns down to 4, that is
still, IMHO way too much data to be moving around.
First ask the member their sex and make your select statement a SELECT
DISTINCT query. That would probably remove around half of the rows
you need to return. If you don't want duplicate data do a SELECT
DISTINCT in the first select. Doing it later in the second query is a
waste of time.
I don't know what your application is doing here, but surely there is
some criteria you know before the first select that would allow you to
filter out most of the data.
Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com
.
- Follow-Ups:
- Re: 2nd layer of filtering?
- From: William \(Bill\) Vaughn
- Re: 2nd layer of filtering?
- Prev by Date: Re: 2nd layer of filtering?
- Next by Date: Re: Managing "Row ID" for Table Inserts
- Previous by thread: Re: 2nd layer of filtering?
- Next by thread: Re: 2nd layer of filtering?
- Index(es):
Relevant Pages
|
