2nd layer of filtering?

microsoft.public.dotnet.framework.adonet

2 Jan 2006 1:34 AM

jonefer

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.

Are you able to do this in ADO.NET with a DataTable?... if not, how do you
go about it?

In DAO, what I would do is write the First Select Statement
qdf.SQL = "Select...."

That would become qMembershipSelect

and then refer to the rewritten query as if it were a table.
it would be nice if qMembershipSelect could be referred to with this
complexity with another dataadapter?

I don't see in ADO.NET being able to write complex SQL statements that refer
to a DataTable (equivalent to a query in Access)?

2 Jan 2006 10:25 AM

Miha Markic [MVP C#]

Hi,

No, DataSet is not a substitute for an in-memory database and it has limited
capabilites for processing.
You might do a manual loop and fill resulting datatable or, if I recally
properly, somebody advertised a product (in this ng) that does SQL
statements on datasets.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"jonefer" <jone***@discussions.microsoft.com> wrote in message
news:90A36CCC-B62D-45CE-A840-02FE03A039E5@microsoft.com...
>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.
>
> Are you able to do this in ADO.NET with a DataTable?... if not, how do you
> go about it?
>
> In DAO, what I would do is write the First Select Statement
> qdf.SQL = "Select...."
>
> That would become qMembershipSelect
>
> and then refer to the rewritten query as if it were a table.
> it would be nice if qMembershipSelect could be referred to with this
> complexity with another dataadapter?
>
> I don't see in ADO.NET being able to write complex SQL statements that
> refer
> to a DataTable (equivalent to a query in Access)?
>
>
>

2 Jan 2006 5:14 PM

jonefer

I'm trying to understand if I am thinking about ADO.NET correctly.
since it is supposed to be an advancement of DAO or ADO.

With DAO, I can rewrite a query using VBA and a querydef object.
Isn't the querydef object a 'Data Access Object'??

So that would mean there is a better approach for this in ADO.NET - right?
What is it?

Would it be better to do this manual loop from the main database, or from a
first resulting dataset?

...And for writing loops would you use a DataReader instead?

Could you or someone in this newsgroup give show me how to write a loop that
would give me the result that my 2nd layer gives:

It basically says in psuedo SQL, Select the Member if the member has a
'Blank' Thru-Date. (so if the member has multiple - blank Thru-Dates they
should all appear)

If the member has no blank Thru-Dates, it should pick the latest one.

If there is a fast way to do this all at once, that would be nice.

If there was a way to rewrite my query in Access from ADO.NET and refer to
that rewritten query in the DataAdapter, (using a view and not a table) that
would also be nice.

Show quote

"Miha Markic [MVP C#]" wrote:

> Hi,
>
> No, DataSet is not a substitute for an in-memory database and it has limited
> capabilites for processing.
> You might do a manual loop and fill resulting datatable or, if I recally
> properly, somebody advertised a product (in this ng) that does SQL
> statements on datasets.
>
> --
> Miha Markic [MVP C#]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>
> "jonefer" <jone***@discussions.microsoft.com> wrote in message
> news:90A36CCC-B62D-45CE-A840-02FE03A039E5@microsoft.com...
> >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.
> >
> > Are you able to do this in ADO.NET with a DataTable?... if not, how do you
> > go about it?
> >
> > In DAO, what I would do is write the First Select Statement
> > qdf.SQL = "Select...."
> >
> > That would become qMembershipSelect
> >
> > and then refer to the rewritten query as if it were a table.
> > it would be nice if qMembershipSelect could be referred to with this
> > complexity with another dataadapter?
> >
> > I don't see in ADO.NET being able to write complex SQL statements that
> > refer
> > to a DataTable (equivalent to a query in Access)?
> >
> >
> >
>
>
>

2 Jan 2006 6:32 PM

Miha Markic [MVP C#]

Just to make sure - ado.net has limited capabilities for processing in
memory data (DataTable) but has no limitations for doing server based
processing as long as you don't need database cursor.
Thus I would suggest you to use either a stored procedure or issue a sql
statement (using same DataAdapter.Fill method to fill the datatable if you
wish).

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"jonefer" <jone***@discussions.microsoft.com> wrote in message
news:835E1702-E5FA-48EF-AE97-4DA0CFD6F795@microsoft.com...
> I'm trying to understand if I am thinking about ADO.NET correctly.
> since it is supposed to be an advancement of DAO or ADO.
>
> With DAO, I can rewrite a query using VBA and a querydef object.
> Isn't the querydef object a 'Data Access Object'??
>
> So that would mean there is a better approach for this in ADO.NET - right?
> What is it?
>
> Would it be better to do this manual loop from the main database, or from
> a
> first resulting dataset?
>
> ..And for writing loops would you use a DataReader instead?
>
> Could you or someone in this newsgroup give show me how to write a loop
> that
> would give me the result that my 2nd layer gives:
>
> It basically says in psuedo SQL, Select the Member if the member has a
> 'Blank' Thru-Date. (so if the member has multiple - blank Thru-Dates they
> should all appear)
>
> If the member has no blank Thru-Dates, it should pick the latest one.
>
> If there is a fast way to do this all at once, that would be nice.
>
> If there was a way to rewrite my query in Access from ADO.NET and refer to
> that rewritten query in the DataAdapter, (using a view and not a table)
> that
> would also be nice.
>
> "Miha Markic [MVP C#]" wrote:
>
>> Hi,
>>
>> No, DataSet is not a substitute for an in-memory database and it has
>> limited
>> capabilites for processing.
>> You might do a manual loop and fill resulting datatable or, if I recally
>> properly, somebody advertised a product (in this ng) that does SQL
>> statements on datasets.
>>
>> --
>> Miha Markic [MVP C#]
>> RightHand .NET consulting & development www.rthand.com
>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>>
>> "jonefer" <jone***@discussions.microsoft.com> wrote in message
>> news:90A36CCC-B62D-45CE-A840-02FE03A039E5@microsoft.com...
>> >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.
>> >
>> > Are you able to do this in ADO.NET with a DataTable?... if not, how do
>> > you
>> > go about it?
>> >
>> > In DAO, what I would do is write the First Select Statement
>> > qdf.SQL = "Select...."
>> >
>> > That would become qMembershipSelect
>> >
>> > and then refer to the rewritten query as if it were a table.
>> > it would be nice if qMembershipSelect could be referred to with this
>> > complexity with another dataadapter?
>> >
>> > I don't see in ADO.NET being able to write complex SQL statements that
>> > refer
>> > to a DataTable (equivalent to a query in Access)?
>> >
>> >
>> >
>>
>>
>>

2 Jan 2006 6:51 PM

Otis Mukinfus

On Sun, 1 Jan 2006 17:34:03 -0800, "jonefer"
<jone***@discussions.microsoft.com> wrote:

Show quote

>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

2 Jan 2006 8:03 PM

William (Bill) Vaughn

Otis, I'm with you. Moving 1.5 million rows to the client is
counter-productive. I would leverage the power of the server (assuming it's
not JET) and do the "filtering" there. The most I recommend to move to the
client would be a couple of hundred rows. Help the user focus their interest
on the needed data. Bring the results to the client--not the database.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Show quote

"Otis Mukinfus" <ph***@emailaddress.com> wrote in message
news:imsir19vr0g8kmhrurth378jl36ugv51s4@4ax.com...
> On Sun, 1 Jan 2006 17:34:03 -0800, "jonefer"
> <jone***@discussions.microsoft.com> 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

2 Jan 2006 10:01 PM

Otis Mukinfus

On Mon, 2 Jan 2006 12:03:52 -0800, "William \(Bill\) Vaughn"
<billvaRemoveT***@nwlink.com> wrote:

>Otis, I'm with you. Moving 1.5 million rows to the client is
>counter-productive. I would leverage the power of the server (assuming it's
>not JET) and do the "filtering" there. The most I recommend to move to the
>client would be a couple of hundred rows. Help the user focus their interest
>on the needed data. Bring the results to the client--not the database.

Thanks, Bill!

I learned RDO from on of your "Hitchhiker" books. Keep up the good
work!

Even if it's Jet, can you imagine asking a user to choose something
from a list of 1.5 million rows. How long would it take you to scroll
throw 1.5 million rows one at a time?

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com