2nd layer of filtering?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)?
 
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.
 
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 said:
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 said:
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)?
Click to expand...
Click to expand...
 
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/

jonefer said:
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 said:
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 said:
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)?
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
[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
 
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.
__________________________________

Otis Mukinfus said:
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.
Click to expand...
[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
Click to expand...
 
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.
Click to expand...

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
 
Back
Top