OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Friday, March 23, 2012
Nesting views?
OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David
nested views lock management
Hi, I have a question about nested views. Is there a way for the view not to place any locks on the underlying tables? I tried to re-write my views and its nested views with "with (nolock)" but when I view the enterprise manager, I still see exclusive and share lock on the tables. Any help would be appreciated. Thanks.
I have wanted to do this before and have tried it; however, views do not work that way. If you want to code with the NOLOCK hint you will need to code it with the actual queries. One alternative, although not an efficient one, would be to create a multi-line TVF instead of a view in which the function consists of a query that contains the NOLOCK hint. I have never used this alternative and I guess I don't really recommend it.
|||Hi , Thanks for the reply.
I was also wondering how about putting the select from view in a store procedure, and set the transaction level to read uncommited. Would that work? This is because I think I am getting a dead lock on a table. for some reason, the select is placing a share lock and an exclusive lock on the underlaying table which make no sense to me, I thought exclusive lock is only for insert, update and delete. Thanks for any response again.
|||Which SQL Server version do you use?
If you use SQL Server 2005, you could use READ_COMMITTED_SNAPSHOT option for you database.
Using snapshot isolation could decrease number of deadlocks, because transaction isolation was implemented with usage row versions.Nested views
When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?
When you say "4 levels deep" do you mean 4 joins? you might want to test it out. There will be an overhead of creating a temp table, inserting data into it, followed by a SELECT from the temp table. If your report will be run by thousands of users simultaneously you might even see a degradation in performance due to tempdb contention.
|||By 4 levels deep I mean View1 is a query of 6 tables and several joins, then view2 use view1 with some more tables and joins or calculations etc etc.
Basically the end result in view4 is too complex to write in 1 query or 1 view so you go as far as you can in view1, then expand that result using view2 etc.
So any ideas on performance??
|||
Off the top of the head, since its not a simple query, I cant think of any, so your best bet is to test it out quickly. You can fire up profiler or use SET STATS IO ON and some others like TIME etc and see if there is any improvement/degradation in performance.
Wednesday, March 21, 2012
Nested tables in client side .RDLC reports
We are developing client side reports (.rdlc) that will require a nested
Parent-Child structure on the report.
The structure shown on the report would appear something like this:
PO# PO Date Invoice #
UPC # Catalog #
UCC-128# Catalog #
The above example would be the Column headings.
Our initial thought was:
Table
Table
Table
Each level needs to be formatted in a table-like format with column headers
and row data.
The data for the above is contained in a DataSet with multiple tables.
Each level can be one or many rows and needs to be grouped accordingly.
After adding a report(.rdlc) to the Visual Studio 2005 project, the report
designer and controls(Table, List, Matrix etc) do not seem to support this
type of nested structure.
You cannot put a table inside another tables detail section.
What would be the best way to accomplish this?Hello,
I would like to know whether the Tables have any relationship between them.
Basically, my suggestion is using the Group in the Table control. You could
include all the three tables in one resultset and using Group to group the
value.
You could refer the BOL to check whether the Group is suitable for your
scenario.
How to: Add a Group to a Table (Report Designer)
http://msdn2.microsoft.com/en-us/library/ms156487.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thank you for your response. What I have to work with is a Dataset with
multiple datatables representing the schema for the data, which I will
receive in xml. So the data will be returned to me in xml also. I have 6 Data
Tables in the dataset with relations between them via foreign keys.
Am I correct in determining what you are suggesting is combining all 6
datatables into one datatable and using this one datatable as the dataset for
my report. And then set up grouping?
If this is correct, what would be the easier way to take datatables with
relations defined and combine them?
Would I need to create a master dataset and then loop through all 6
DataTables and manually build the main table?
Thanks in advance!
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to know whether the Tables have any relationship between them.
> Basically, my suggestion is using the Group in the Table control. You could
> include all the three tables in one resultset and using Group to group the
> value.
> You could refer the BOL to check whether the Group is suitable for your
> scenario.
> How to: Add a Group to a Table (Report Designer)
> http://msdn2.microsoft.com/en-us/library/ms156487.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
Why not using the SQL statement to retrive the data from the database
directly?
Also, you could add some column in the datatable and using the Expression
to refer the other table's data.
For more detailed information about the expression, please refer following
article:
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.a
spx
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hello,
Does my suggestion make any sense to you?
If you have any concerns, please feel free to let me know
Sincerely,
Wei Lu
Microsoft Online Community Support|||Wei Lu,
I'm not sure if I understand what you are suggesting with column expressions.
We are developing an application that will display Reporting Servives clinet
(.rdlc) files via the ReportViewer.
These reports need to be completely dynamic. The Datset Schema, data, and
the .rdlc Report format file must all be dynamic.
The Dataset Schema Xml is stored on the client and will consist of several
Data Tables with relationships defined connectiong all of the tables.
An .rdlc report can only be bound to one datatable as the source for the
report data.
What we need to accomplish is to pull all of the data from all of the
DataTables into one to use as the report source.
This is normally done through a SQL join against the database. Since the
schema is completely dynamic, we do not have any database tables to run a
JOIN.
We need to be able to simulate this join using only DataSet and DataTables.
We've investigated using the JoinView class to join fields from all of these
tables into one but it looks as if JoinView can not support joining all of
these tables. Joinview only takes one table and operates on Parent and Child
relationships from this one table.
If I start at the table at the lowest level in the heirarchy, it seems as if
I can only pull fields from the parent of this table, and not fields from the
rest of the tables (Parent of Parent, Parent of thisParent etc).
1) Is there a way to do this using JoinView to pull data from all tables
into one for the report?
2) Can this be done using relationships by loopiong through all tables?
3) Am I approaching this the wrong way and if so do you have any suggestions
The dynamic schema is read into a Dataset at runtime with the data. So I
will not even know how many tables, columns, fields, and what data is
contained until then.
"Wei Lu [MSFT]" wrote:
> Hello,
> Does my suggestion make any sense to you?
> If you have any concerns, please feel free to let me know
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
>|||Hello,
I am not sure why you want to dynamic the dataset.
If so, I think it will be very hard to build up the report. My suggestion
is to use the .NET to generate a rdlc file by your custom application.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
I have the exact same problem but it's not possible for me at this
point to create a new datatable in the dataset with a single level
structure.
I have a DataSet with two datatables and a relation between them. Let's
say Invoice and InvoiceDetails.
I also have a local report with a grouping structure to represent that.
The thing is that only the InvoiceDetails lines are shown in the
report. Is there a way I can tell a textbox that the Field I want to
show is from a particular datatable? otherwise, is there a way to tell
a Field which datatable it belongs to?
Any help would be appreciated
TIA,
Sebasti=E1n
On Oct 9, 8:09 am, w...@.online.microsoft.com (Wei Lu [MSFT]) wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E
Parent-Child structure on the report.
The structure shown on the report would appear something like this:
PO# PO Date Invoice #
UPC # Catalog #
UCC-128# Catalog #
The above example would be the Column headings.
Our initial thought was:
Table
Table
Table
Each level needs to be formatted in a table-like format with column headers
and row data.
The data for the above is contained in a DataSet with multiple tables.
Each level can be one or many rows and needs to be grouped accordingly.
After adding a report(.rdlc) to the Visual Studio 2005 project, the report
designer and controls(Table, List, Matrix etc) do not seem to support this
type of nested structure.
You cannot put a table inside another tables detail section.
What would be the best way to accomplish this?Hello,
I would like to know whether the Tables have any relationship between them.
Basically, my suggestion is using the Group in the Table control. You could
include all the three tables in one resultset and using Group to group the
value.
You could refer the BOL to check whether the Group is suitable for your
scenario.
How to: Add a Group to a Table (Report Designer)
http://msdn2.microsoft.com/en-us/library/ms156487.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thank you for your response. What I have to work with is a Dataset with
multiple datatables representing the schema for the data, which I will
receive in xml. So the data will be returned to me in xml also. I have 6 Data
Tables in the dataset with relations between them via foreign keys.
Am I correct in determining what you are suggesting is combining all 6
datatables into one datatable and using this one datatable as the dataset for
my report. And then set up grouping?
If this is correct, what would be the easier way to take datatables with
relations defined and combine them?
Would I need to create a master dataset and then loop through all 6
DataTables and manually build the main table?
Thanks in advance!
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to know whether the Tables have any relationship between them.
> Basically, my suggestion is using the Group in the Table control. You could
> include all the three tables in one resultset and using Group to group the
> value.
> You could refer the BOL to check whether the Group is suitable for your
> scenario.
> How to: Add a Group to a Table (Report Designer)
> http://msdn2.microsoft.com/en-us/library/ms156487.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
Why not using the SQL statement to retrive the data from the database
directly?
Also, you could add some column in the datatable and using the Expression
to refer the other table's data.
For more detailed information about the expression, please refer following
article:
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.a
spx
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hello,
Does my suggestion make any sense to you?
If you have any concerns, please feel free to let me know
Sincerely,
Wei Lu
Microsoft Online Community Support|||Wei Lu,
I'm not sure if I understand what you are suggesting with column expressions.
We are developing an application that will display Reporting Servives clinet
(.rdlc) files via the ReportViewer.
These reports need to be completely dynamic. The Datset Schema, data, and
the .rdlc Report format file must all be dynamic.
The Dataset Schema Xml is stored on the client and will consist of several
Data Tables with relationships defined connectiong all of the tables.
An .rdlc report can only be bound to one datatable as the source for the
report data.
What we need to accomplish is to pull all of the data from all of the
DataTables into one to use as the report source.
This is normally done through a SQL join against the database. Since the
schema is completely dynamic, we do not have any database tables to run a
JOIN.
We need to be able to simulate this join using only DataSet and DataTables.
We've investigated using the JoinView class to join fields from all of these
tables into one but it looks as if JoinView can not support joining all of
these tables. Joinview only takes one table and operates on Parent and Child
relationships from this one table.
If I start at the table at the lowest level in the heirarchy, it seems as if
I can only pull fields from the parent of this table, and not fields from the
rest of the tables (Parent of Parent, Parent of thisParent etc).
1) Is there a way to do this using JoinView to pull data from all tables
into one for the report?
2) Can this be done using relationships by loopiong through all tables?
3) Am I approaching this the wrong way and if so do you have any suggestions
The dynamic schema is read into a Dataset at runtime with the data. So I
will not even know how many tables, columns, fields, and what data is
contained until then.
"Wei Lu [MSFT]" wrote:
> Hello,
> Does my suggestion make any sense to you?
> If you have any concerns, please feel free to let me know
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
>|||Hello,
I am not sure why you want to dynamic the dataset.
If so, I think it will be very hard to build up the report. My suggestion
is to use the .NET to generate a rdlc file by your custom application.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
I have the exact same problem but it's not possible for me at this
point to create a new datatable in the dataset with a single level
structure.
I have a DataSet with two datatables and a relation between them. Let's
say Invoice and InvoiceDetails.
I also have a local report with a grouping structure to represent that.
The thing is that only the InvoiceDetails lines are shown in the
report. Is there a way I can tell a textbox that the Field I want to
show is from a particular datatable? otherwise, is there a way to tell
a Field which datatable it belongs to?
Any help would be appreciated
TIA,
Sebasti=E1n
On Oct 9, 8:09 am, w...@.online.microsoft.com (Wei Lu [MSFT]) wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E
Nested Tables
Hi
I have 3 tables each with a diferent dataset's, is there any way i can nest 2 of the tables inside one of the other table's group's?
Any other way tath i could repeat 3 tables in some grouping method?
Using different datasets inside one top-level table is not currently supported, so you cannot nest the two tables inside the other table. But you can create two subreports and put the two tables in the subreports respectively, then nest the subreports in the other table.|||That's exactly what i did. Was hopping there was some other way not using SubReports.
Never the less it works fine with Sub-Reports , thank you for your time
Nested Tables
This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C75C0A.08318580
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all
Wanting to create a report with a table inside another table. I've set = the dataset for each table to the relevant datasources. However when I = try set textboxes of the inner table to fields from dataset of the ineer = table, I get the following error message.
The value expression for the textbox 'textbox12' refers to the field = 'NAME'. Report item expressions can only refer to fields within the = current data set scope or, if inside an aggregate, the specified data = set scope.
I also get this error when trying to set a filter on the inner table to = a value on the outer table.
Not sure if this is the right newsgroup, if not someone please let me = know where I can get an answer
Using SQL Reporting services 2000, Visual Studio.NET 2003
Thanks
George
--=_NextPart_000_000B_01C75C0A.08318580
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi all
Wanting to create a report with a = table inside another table. I've set the dataset for each table to = the relevant datasources. However when I try set textboxes of the inner = table to fields from dataset of the ineer table, I get the following error message.
The value expression for the = textbox =91textbox12=92 refers to the field =91NAME=92. Report item = expressions can only refer to fields within the current data set scope or, if inside an = aggregate, the specified data set scope.
I also get this error when trying to = set a filter on the inner table to a value on the outer table.
Not sure if this is the right = newsgroup, if not someone please let me know where I can get an answer
Using SQL Reporting services 2000, = Visual Studio.NET 2003
Thanks
George
--=_NextPart_000_000B_01C75C0A.08318580--On Feb 28, 9:00 pm, "_george" <none@.nojne@.none> wrote:
> Hi all
> Wanting to create a report with a table inside another table. I've set the dataset for each table to the relevant datasources. However when I try set textboxes of the inner table to fields from dataset of the ineer table, I get the following error message.
> The value expression for the textbox 'textbox12' refers to the field 'NAME'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
> I also get this error when trying to set a filter on the inner table to a value on the outer table.
> Not sure if this is the right newsgroup, if not someone please let me know where I can get an answer
> Using SQL Reporting services 2000, Visual Studio.NET 2003
> Thanks
> George
You might have to explicitly reference the dataset. For example, if
the inside table is table 2, you might have to use an expression like
the following:
=Max(Fields!NAME.Value, "dsTable2sDataSetName")
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Tried that. Still same error. I suppose I'm asking if this is possible?
Thanks
George
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1172720239.500267.94860@.s48g2000cws.googlegroups.com...
> On Feb 28, 9:00 pm, "_george" <none@.nojne@.none> wrote:
>> Hi all
>> Wanting to create a report with a table inside another table. I've set
>> the dataset for each table to the relevant datasources. However when I
>> try set textboxes of the inner table to fields from dataset of the ineer
>> table, I get the following error message.
>> The value expression for the textbox 'textbox12' refers to the field
>> 'NAME'. Report item expressions can only refer to fields within the
>> current data set scope or, if inside an aggregate, the specified data set
>> scope.
>> I also get this error when trying to set a filter on the inner table to a
>> value on the outer table.
>> Not sure if this is the right newsgroup, if not someone please let me
>> know where I can get an answer
>> Using SQL Reporting services 2000, Visual Studio.NET 2003
>> Thanks
>> George
>
> You might have to explicitly reference the dataset. For example, if
> the inside table is table 2, you might have to use an expression like
> the following:
> =Max(Fields!NAME.Value, "dsTable2sDataSetName")
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>sql
--=_NextPart_000_000B_01C75C0A.08318580
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all
Wanting to create a report with a table inside another table. I've set = the dataset for each table to the relevant datasources. However when I = try set textboxes of the inner table to fields from dataset of the ineer = table, I get the following error message.
The value expression for the textbox 'textbox12' refers to the field = 'NAME'. Report item expressions can only refer to fields within the = current data set scope or, if inside an aggregate, the specified data = set scope.
I also get this error when trying to set a filter on the inner table to = a value on the outer table.
Not sure if this is the right newsgroup, if not someone please let me = know where I can get an answer
Using SQL Reporting services 2000, Visual Studio.NET 2003
Thanks
George
--=_NextPart_000_000B_01C75C0A.08318580
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi all
Wanting to create a report with a = table inside another table. I've set the dataset for each table to = the relevant datasources. However when I try set textboxes of the inner = table to fields from dataset of the ineer table, I get the following error message.
The value expression for the = textbox =91textbox12=92 refers to the field =91NAME=92. Report item = expressions can only refer to fields within the current data set scope or, if inside an = aggregate, the specified data set scope.
I also get this error when trying to = set a filter on the inner table to a value on the outer table.
Not sure if this is the right = newsgroup, if not someone please let me know where I can get an answer
Using SQL Reporting services 2000, = Visual Studio.NET 2003
Thanks
George
--=_NextPart_000_000B_01C75C0A.08318580--On Feb 28, 9:00 pm, "_george" <none@.nojne@.none> wrote:
> Hi all
> Wanting to create a report with a table inside another table. I've set the dataset for each table to the relevant datasources. However when I try set textboxes of the inner table to fields from dataset of the ineer table, I get the following error message.
> The value expression for the textbox 'textbox12' refers to the field 'NAME'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
> I also get this error when trying to set a filter on the inner table to a value on the outer table.
> Not sure if this is the right newsgroup, if not someone please let me know where I can get an answer
> Using SQL Reporting services 2000, Visual Studio.NET 2003
> Thanks
> George
You might have to explicitly reference the dataset. For example, if
the inside table is table 2, you might have to use an expression like
the following:
=Max(Fields!NAME.Value, "dsTable2sDataSetName")
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Tried that. Still same error. I suppose I'm asking if this is possible?
Thanks
George
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1172720239.500267.94860@.s48g2000cws.googlegroups.com...
> On Feb 28, 9:00 pm, "_george" <none@.nojne@.none> wrote:
>> Hi all
>> Wanting to create a report with a table inside another table. I've set
>> the dataset for each table to the relevant datasources. However when I
>> try set textboxes of the inner table to fields from dataset of the ineer
>> table, I get the following error message.
>> The value expression for the textbox 'textbox12' refers to the field
>> 'NAME'. Report item expressions can only refer to fields within the
>> current data set scope or, if inside an aggregate, the specified data set
>> scope.
>> I also get this error when trying to set a filter on the inner table to a
>> value on the outer table.
>> Not sure if this is the right newsgroup, if not someone please let me
>> know where I can get an answer
>> Using SQL Reporting services 2000, Visual Studio.NET 2003
>> Thanks
>> George
>
> You might have to explicitly reference the dataset. For example, if
> the inside table is table 2, you might have to use an expression like
> the following:
> =Max(Fields!NAME.Value, "dsTable2sDataSetName")
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>sql
Nested Tables
Using SSRS 2005, is there anyway to embed a table in a table and link the two together in a parent child relationship?
R
WHat do you want to achieve ? It sure can be accomplished by using the standard functionality.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Nested Tables
Hi
I have 3 tables each with a diferent dataset's, is there any way i can nest 2 of the tables inside one of the other table's group's?
Any other way tath i could repeat 3 tables in some grouping method?
Using different datasets inside one top-level table is not currently supported, so you cannot nest the two tables inside the other table. But you can create two subreports and put the two tables in the subreports respectively, then nest the subreports in the other table.|||That's exactly what i did. Was hopping there was some other way not using SubReports.
Never the less it works fine with Sub-Reports , thank you for your time
Nested Tables
Hi can you insert nested tables in a table already exsisting in sql server and if so how would i go about do such a thing
You can in SQL 2005. See http://msdn2.microsoft.com/en-us/library/ms175659.aspx. You could also insert an XML blob, but this would be more like nesting a row. I would not recomend nexting XML. It uses up too much space because you end up with non-normalized data and makes selects for values in the XML field very slow, at best.
Thanks in advance
Quote:
Originally Posted by Taftheman
Hi can you insert nested tables in a table already exsisting in sql server and if so how would i go about do such a thing
Thanks in advance
You can in SQL 2005. See http://msdn2.microsoft.com/en-us/library/ms175659.aspx. You could also insert an XML blob, but this would be more like nesting a row. I would not recomend nexting XML. It uses up too much space because you end up with non-normalized data and makes selects for values in the XML field very slow, at best.
That said, be careful. This is pretty advanced stuff and hard to support long term.
NESTED SQL QUESTION and aggregate function
I have have three tables that I need to pull information from, and
return selected rows. Sorry for the psudeo-code for the tables, but it
should give you an idea of their (simplified) structure:
Inspections (
InspectionID int (PK),
LocationID int (FK),
InspectorID int (FK),
InspectionDate datetime
)
Inspectors (
InspectorID int (PK)
InspectorName varchar(50)
)
Location (
LocationID int (PK)
LocationName varchar(50)
)
Here's the data I need displayed as it would be shown in a "flat"
return:
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101)
ORDER BY p.InspectorName, LastUpdate
would return results:
Joe Inspector DEARBORN 2006-02-09 10:00:07
Joe Inspector DEARBORN 2006-02-09 10:10:04
Joe Inspector DEARBORN 2006-02-09 10:19:19
John Smith ANN ARBOR 2006-02-09 14:20:35
John Smith DEXTER 2006-02-09 14:21:38
Jane Doe CLINTON 2006-02-09 11:40:49
Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
Now, this is what I actually need: I need ONLY the first row (ie,
earliest time of inspection) for EACH inspector, regardless of
location. So my result set would look like:
Joe Inspector DEARBORN 2006-02-09 10:00:07
John Smith ANN ARBOR 2006-02-09 14:20:35
Jane Doe CLINTON 2006-02-09 11:40:49
Another Guy <NULL> <NULL>
Also, if the inspector has no inspections for that day, I would still
like to see the inspector name (note last row)
I'll be passing in the date (not the time) as a parameter.
I have tried using a nested SQL statement with an aggreagate MIN on the
InspectionTime, but I can't seem to get just the FIRST row for each
inspector to display - it will just give me every inspection for that
inspector.
Any help or insight will be greatly appreciated.
Christiani think this will work...
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
where inspectiondate = (select min(inspectiondate) from inspections
where CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101) group by inspectorid)
ORDER BY p.InspectorName, LastUpdate
post DDL and insert statement for more clear solutions|||SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName, LocationID
) t1
ON p.InspectorId = t1.InspectorId
INNER JOIN Location l ON t1.LocationId = l.LocationID
ORDER BY p.InspectorName, t1.EaliestInspection
"kaczmar2@.hotmail.com" wrote:
> I have have three tables that I need to pull information from, and
> return selected rows. Sorry for the psudeo-code for the tables, but it
> should give you an idea of their (simplified) structure:
> Inspections (
> InspectionID int (PK),
> LocationID int (FK),
> InspectorID int (FK),
> InspectionDate datetime
> )
> Inspectors (
> InspectorID int (PK)
> InspectorName varchar(50)
> )
> Location (
> LocationID int (PK)
> LocationName varchar(50)
> )
> Here's the data I need displayed as it would be shown in a "flat"
> return:
> SELECT
> p.InspectorName,
> l.LocationName,
> i.InspectionDate
> FROM
> Inspectors p
> LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> '02/09/2006', 101)
> ORDER BY p.InspectorName, LastUpdate
> would return results:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> Joe Inspector DEARBORN 2006-02-09 10:10:04
> Joe Inspector DEARBORN 2006-02-09 10:19:19
> John Smith ANN ARBOR 2006-02-09 14:20:35
> John Smith DEXTER 2006-02-09 14:21:38
> Jane Doe CLINTON 2006-02-09 11:40:49
> Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
> Now, this is what I actually need: I need ONLY the first row (ie,
> earliest time of inspection) for EACH inspector, regardless of
> location. So my result set would look like:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> John Smith ANN ARBOR 2006-02-09 14:20:35
> Jane Doe CLINTON 2006-02-09 11:40:49
> Another Guy <NULL> <NULL>
> Also, if the inspector has no inspections for that day, I would still
> like to see the inspector name (note last row)
> I'll be passing in the date (not the time) as a parameter.
> I have tried using a nested SQL statement with an aggreagate MIN on the
> InspectionTime, but I can't seem to get just the FIRST row for each
> inspector to display - it will just give me every inspection for that
> inspector.
> Any help or insight will be greatly appreciated.
> Christian
>|||Sorry, that last JOIN should be LEFT, not INNER.
--
"Mark Williams" wrote:
> SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
> ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Tod
ay')
> FROM Inspectors P
> LEFT JOIN
> (
> SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection
"
> FROM Inspections
> WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
> GROUP BY InspectorName, LocationID
> ) t1
> ON p.InspectorId = t1.InspectorId
> INNER JOIN Location l ON t1.LocationId = l.LocationID
> ORDER BY p.InspectorName, t1.EaliestInspection
> --
> "kaczmar2@.hotmail.com" wrote:
>|||Thank you very much for your feedback. This gets me what I want except
for one thing: It shows the earliest time for each location. I want
to show the earliest time for each inspector regardless of location,
but I do want to see the lcoation in the result set. So I can't group
by location. This is your result set:
Tony Inspector ANN ARBOR 16:13:40
Tony Inspector YPSILANTI 17:19:08
Joe Schmoe PLAINWELL 13:12:39
Jane Doe GRAND RAPIDS 11:42:27
Jane Doe GRANDVILLE 12:48:00
Any ideas on how to get the earliest time regardless of location?
Thank you for your continued help.
Mark Williams wrote:
> Sorry, that last JOIN should be LEFT, not INNER.
> --
>
> "Mark Williams" wrote:
>|||Terribly sorry,
SELECT p.InspectorName, ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT InspectorId, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName
) t1
INNER JOIN
(SELECT i.LocationId, l.LocationName FROM Inspections i
INNER JOIN Locations l ON i.LocationId = l.LocationId) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
"kaczmar2@.hotmail.com" wrote:
> Thank you very much for your feedback. This gets me what I want except
> for one thing: It shows the earliest time for each location. I want
> to show the earliest time for each inspector regardless of location,
> but I do want to see the lcoation in the result set. So I can't group
> by location. This is your result set:
> Tony Inspector ANN ARBOR 16:13:40
> Tony Inspector YPSILANTI 17:19:08
> Joe Schmoe PLAINWELL 13:12:39
> Jane Doe GRAND RAPIDS 11:42:27
> Jane Doe GRANDVILLE 12:48:00
> Any ideas on how to get the earliest time regardless of location?
> Thank you for your continued help.
>
> Mark Williams wrote:
>|||Thank you for the reply. I mad to modify the query since your subquery
returns more than one value. "where inspectiondate = (.." was changed
to "where inspectiondate IN (.."
This looks to give me coreect results except for those inspectors that
did not work that day. I would like to show them in the results with
NULL data.
I have attached the DDL and INSERT statements as requested:
CREATE TABLE [dbo].[Inspections] (
[InspectionID] [int] NOT NULL ,
[LocationID] [int] NOT NULL ,
[InspectorID] [int] NOT NULL ,
[InspectionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Inspectors] (
[InspectorID] [int] NOT NULL ,
[InspectorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Location] (
[LocationID] [int] NOT NULL ,
[LocationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspections] ADD
CONSTRAINT [PK_Inspections] PRIMARY KEY CLUSTERED
(
[InspectionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspectors] ADD
CONSTRAINT [PK_Inspectors] PRIMARY KEY CLUSTERED
(
[InspectorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Location] ADD
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationID]
) ON [PRIMARY]
GO
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(1,'Joe Inspector')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(2,'Jane Doe')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(3,'John Smith')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(4,'New Guy')
INSERT INTO Location(LocationID,LocationName)
VALUES(1,'Detroit')
INSERT INTO Location(LocationID,LocationName)
VALUES(2,'Ann Arbor')
INSERT INTO Location(LocationID,LocationName)
VALUES(3,'Royal Oak')
INSERT INTO Location(LocationID,LocationName)
VALUES(4,'Monroe')
INSERT INTO Location(LocationID,LocationName)
VALUES(5,'Dearborn')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(1,1,1,'2/9/2006 9:10 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(2,1,1,'2/9/2006 10:15 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(3,2,2,'2/9/2006 7:30 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(4,3,2,'2/9/2006 11:00 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(5,4,3,'2/9/2006 2:00 PM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(6,5,3,'2/9/2006 1:00 PM')
Thank you very much for your help.|||If you have the proper constraints on your table, you will almost NEVER
use CONVERT()) in a query. Why keep mopping the floor and losing the
ability to use indexes when a simple CHECK() can trim off the time part
of a DATETIME? Fix the leak!
You might also want to learn about ISO-8601 and the SQL Standards for
temporal data, just in case you need to use ISO Standards some day :))
After all, you are one of the few posters lately who actually followed
the ISO-11179 naming conventions!
Your origianl query looks useful in itself, so you might put it in a
VIEW, but this will give you what you asked for
SELECT inspector_name, location_name, MIN(inspection_date)
-- , MAX(inspection_date) could be useful, too!
FROM (SELECT P.inspector_name, L.location_name, I.inspection_date
FROM Inspectors AS P
LEFT OUTER JOIN
Inspections AS I
ON P.inspector_id = I.inspector_id
LEFT OUTER JOIN
Locations AS L
ON I.location_id = L.Location_id
WHERE I.inspection_date '2006-02-09')
AS X(inspector_name, location_name, inspection_date)
GROUP BY X.inspector_name, X.location_name;|||Mark-
Thank you very much, this worked! I just had to tweak one of the
joins. Here is the cleaned up, final query:
SELECT
p.InspectorName,
ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection
Today')
FROM Inspectors p
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT i.InspectorId, MIN(i.InspectionDate) AS "EarliestInspection"
FROM Inspections i
WHERE DATEDIFF(dd, i.InspectionDate, '20060209') = 0
GROUP BY i.InspectorId /* name */
) t1
INNER JOIN
(
SELECT i.LocationId, l.LocationName, i.InspectionDate, i.InspectorID
-- added
FROM Inspections i
INNER JOIN Location l ON i.LocationId = l.LocationId
) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
Thanks again for your help!!!|||CELKO-
Thank you for your input. I probably did not explain my complete
intentions when you saw the CONVERT function used to strip of the time.
I do need the time information, but I need to group by the day for
this query, that is why I was using convert. And yes, I probably
should be aware of my naming conventions/syntax. Note that I posted
abriged tables/data/queries to simplify the example to the group.sql
return selected rows. Sorry for the psudeo-code for the tables, but it
should give you an idea of their (simplified) structure:
Inspections (
InspectionID int (PK),
LocationID int (FK),
InspectorID int (FK),
InspectionDate datetime
)
Inspectors (
InspectorID int (PK)
InspectorName varchar(50)
)
Location (
LocationID int (PK)
LocationName varchar(50)
)
Here's the data I need displayed as it would be shown in a "flat"
return:
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101)
ORDER BY p.InspectorName, LastUpdate
would return results:
Joe Inspector DEARBORN 2006-02-09 10:00:07
Joe Inspector DEARBORN 2006-02-09 10:10:04
Joe Inspector DEARBORN 2006-02-09 10:19:19
John Smith ANN ARBOR 2006-02-09 14:20:35
John Smith DEXTER 2006-02-09 14:21:38
Jane Doe CLINTON 2006-02-09 11:40:49
Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
Now, this is what I actually need: I need ONLY the first row (ie,
earliest time of inspection) for EACH inspector, regardless of
location. So my result set would look like:
Joe Inspector DEARBORN 2006-02-09 10:00:07
John Smith ANN ARBOR 2006-02-09 14:20:35
Jane Doe CLINTON 2006-02-09 11:40:49
Another Guy <NULL> <NULL>
Also, if the inspector has no inspections for that day, I would still
like to see the inspector name (note last row)
I'll be passing in the date (not the time) as a parameter.
I have tried using a nested SQL statement with an aggreagate MIN on the
InspectionTime, but I can't seem to get just the FIRST row for each
inspector to display - it will just give me every inspection for that
inspector.
Any help or insight will be greatly appreciated.
Christiani think this will work...
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
where inspectiondate = (select min(inspectiondate) from inspections
where CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101) group by inspectorid)
ORDER BY p.InspectorName, LastUpdate
post DDL and insert statement for more clear solutions|||SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName, LocationID
) t1
ON p.InspectorId = t1.InspectorId
INNER JOIN Location l ON t1.LocationId = l.LocationID
ORDER BY p.InspectorName, t1.EaliestInspection
"kaczmar2@.hotmail.com" wrote:
> I have have three tables that I need to pull information from, and
> return selected rows. Sorry for the psudeo-code for the tables, but it
> should give you an idea of their (simplified) structure:
> Inspections (
> InspectionID int (PK),
> LocationID int (FK),
> InspectorID int (FK),
> InspectionDate datetime
> )
> Inspectors (
> InspectorID int (PK)
> InspectorName varchar(50)
> )
> Location (
> LocationID int (PK)
> LocationName varchar(50)
> )
> Here's the data I need displayed as it would be shown in a "flat"
> return:
> SELECT
> p.InspectorName,
> l.LocationName,
> i.InspectionDate
> FROM
> Inspectors p
> LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> '02/09/2006', 101)
> ORDER BY p.InspectorName, LastUpdate
> would return results:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> Joe Inspector DEARBORN 2006-02-09 10:10:04
> Joe Inspector DEARBORN 2006-02-09 10:19:19
> John Smith ANN ARBOR 2006-02-09 14:20:35
> John Smith DEXTER 2006-02-09 14:21:38
> Jane Doe CLINTON 2006-02-09 11:40:49
> Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
> Now, this is what I actually need: I need ONLY the first row (ie,
> earliest time of inspection) for EACH inspector, regardless of
> location. So my result set would look like:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> John Smith ANN ARBOR 2006-02-09 14:20:35
> Jane Doe CLINTON 2006-02-09 11:40:49
> Another Guy <NULL> <NULL>
> Also, if the inspector has no inspections for that day, I would still
> like to see the inspector name (note last row)
> I'll be passing in the date (not the time) as a parameter.
> I have tried using a nested SQL statement with an aggreagate MIN on the
> InspectionTime, but I can't seem to get just the FIRST row for each
> inspector to display - it will just give me every inspection for that
> inspector.
> Any help or insight will be greatly appreciated.
> Christian
>|||Sorry, that last JOIN should be LEFT, not INNER.
--
"Mark Williams" wrote:
> SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
> ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Tod
ay')
> FROM Inspectors P
> LEFT JOIN
> (
> SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection
"
> FROM Inspections
> WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
> GROUP BY InspectorName, LocationID
> ) t1
> ON p.InspectorId = t1.InspectorId
> INNER JOIN Location l ON t1.LocationId = l.LocationID
> ORDER BY p.InspectorName, t1.EaliestInspection
> --
> "kaczmar2@.hotmail.com" wrote:
>|||Thank you very much for your feedback. This gets me what I want except
for one thing: It shows the earliest time for each location. I want
to show the earliest time for each inspector regardless of location,
but I do want to see the lcoation in the result set. So I can't group
by location. This is your result set:
Tony Inspector ANN ARBOR 16:13:40
Tony Inspector YPSILANTI 17:19:08
Joe Schmoe PLAINWELL 13:12:39
Jane Doe GRAND RAPIDS 11:42:27
Jane Doe GRANDVILLE 12:48:00
Any ideas on how to get the earliest time regardless of location?
Thank you for your continued help.
Mark Williams wrote:
> Sorry, that last JOIN should be LEFT, not INNER.
> --
>
> "Mark Williams" wrote:
>|||Terribly sorry,
SELECT p.InspectorName, ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT InspectorId, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName
) t1
INNER JOIN
(SELECT i.LocationId, l.LocationName FROM Inspections i
INNER JOIN Locations l ON i.LocationId = l.LocationId) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
"kaczmar2@.hotmail.com" wrote:
> Thank you very much for your feedback. This gets me what I want except
> for one thing: It shows the earliest time for each location. I want
> to show the earliest time for each inspector regardless of location,
> but I do want to see the lcoation in the result set. So I can't group
> by location. This is your result set:
> Tony Inspector ANN ARBOR 16:13:40
> Tony Inspector YPSILANTI 17:19:08
> Joe Schmoe PLAINWELL 13:12:39
> Jane Doe GRAND RAPIDS 11:42:27
> Jane Doe GRANDVILLE 12:48:00
> Any ideas on how to get the earliest time regardless of location?
> Thank you for your continued help.
>
> Mark Williams wrote:
>|||Thank you for the reply. I mad to modify the query since your subquery
returns more than one value. "where inspectiondate = (.." was changed
to "where inspectiondate IN (.."
This looks to give me coreect results except for those inspectors that
did not work that day. I would like to show them in the results with
NULL data.
I have attached the DDL and INSERT statements as requested:
CREATE TABLE [dbo].[Inspections] (
[InspectionID] [int] NOT NULL ,
[LocationID] [int] NOT NULL ,
[InspectorID] [int] NOT NULL ,
[InspectionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Inspectors] (
[InspectorID] [int] NOT NULL ,
[InspectorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Location] (
[LocationID] [int] NOT NULL ,
[LocationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspections] ADD
CONSTRAINT [PK_Inspections] PRIMARY KEY CLUSTERED
(
[InspectionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspectors] ADD
CONSTRAINT [PK_Inspectors] PRIMARY KEY CLUSTERED
(
[InspectorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Location] ADD
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationID]
) ON [PRIMARY]
GO
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(1,'Joe Inspector')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(2,'Jane Doe')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(3,'John Smith')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(4,'New Guy')
INSERT INTO Location(LocationID,LocationName)
VALUES(1,'Detroit')
INSERT INTO Location(LocationID,LocationName)
VALUES(2,'Ann Arbor')
INSERT INTO Location(LocationID,LocationName)
VALUES(3,'Royal Oak')
INSERT INTO Location(LocationID,LocationName)
VALUES(4,'Monroe')
INSERT INTO Location(LocationID,LocationName)
VALUES(5,'Dearborn')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(1,1,1,'2/9/2006 9:10 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(2,1,1,'2/9/2006 10:15 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(3,2,2,'2/9/2006 7:30 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(4,3,2,'2/9/2006 11:00 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(5,4,3,'2/9/2006 2:00 PM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(6,5,3,'2/9/2006 1:00 PM')
Thank you very much for your help.|||If you have the proper constraints on your table, you will almost NEVER
use CONVERT()) in a query. Why keep mopping the floor and losing the
ability to use indexes when a simple CHECK() can trim off the time part
of a DATETIME? Fix the leak!
You might also want to learn about ISO-8601 and the SQL Standards for
temporal data, just in case you need to use ISO Standards some day :))
After all, you are one of the few posters lately who actually followed
the ISO-11179 naming conventions!
Your origianl query looks useful in itself, so you might put it in a
VIEW, but this will give you what you asked for
SELECT inspector_name, location_name, MIN(inspection_date)
-- , MAX(inspection_date) could be useful, too!
FROM (SELECT P.inspector_name, L.location_name, I.inspection_date
FROM Inspectors AS P
LEFT OUTER JOIN
Inspections AS I
ON P.inspector_id = I.inspector_id
LEFT OUTER JOIN
Locations AS L
ON I.location_id = L.Location_id
WHERE I.inspection_date '2006-02-09')
AS X(inspector_name, location_name, inspection_date)
GROUP BY X.inspector_name, X.location_name;|||Mark-
Thank you very much, this worked! I just had to tweak one of the
joins. Here is the cleaned up, final query:
SELECT
p.InspectorName,
ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection
Today')
FROM Inspectors p
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT i.InspectorId, MIN(i.InspectionDate) AS "EarliestInspection"
FROM Inspections i
WHERE DATEDIFF(dd, i.InspectionDate, '20060209') = 0
GROUP BY i.InspectorId /* name */
) t1
INNER JOIN
(
SELECT i.LocationId, l.LocationName, i.InspectionDate, i.InspectorID
-- added
FROM Inspections i
INNER JOIN Location l ON i.LocationId = l.LocationId
) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
Thanks again for your help!!!|||CELKO-
Thank you for your input. I probably did not explain my complete
intentions when you saw the CONVERT function used to strip of the time.
I do need the time information, but I need to group by the day for
this query, that is why I was using convert. And yes, I probably
should be aware of my naming conventions/syntax. Note that I posted
abriged tables/data/queries to simplify the example to the group.sql
Nested Select to Join three tables into one result set
I'll simplify the table structure that I've inherited in order to try to
explain what I need.
Three tables - ISSUES, USERS and ASSIGN:
ISSUES
IDRecord - Primary Key
Description
DateEntered
USERS
IDRecord- Primary Key
LastName
FirstName
ASSIGN
IDRecord - Primary Key
IDDefRec - matches to IDRecord in ISSUES
IDUser - matches to IDRecord in USERS
What I want is a result set for all ISSUES entered after 7/1/2005 (for
example) that includes all of the columns from ISSUES and the FirstName and
LastName of the last user assigned to the ISSUE. The ASSIGN table can
contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
So I figure I just need to get the TOP 1 of the ASSIGN table that matches
the ISSUE and get the corresponding USER name. I just can't figure out how
to do it in one SELECT statement.
JeffWhat the first rule of a data model' A data element has one and only
one name in a schema. So what is this magical "record_id" that appears
to be everywhere?
And why don' t you know that a row and record are totally different
concepts? Why don't you use ISO-8601 Standard date formats? Why did
you put the qualifier in the front of the names, in violation of the
ISO-11179 rules for metadata?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
If you knew what a key was, followed ISO Standards, and underdstood DRI
action your non-existent DDL would look like this:
Why is there no resolution date in your issues? The model of time in
SQL is durations, not single dates.
CREATE TABLE Issues
(issue_nbr INTEGER NOT NULL PRIMARY KEY,
issue_description VARCHAR(255) NOT NULL);
Create a dummy user zero called "To Be Determined" or '{{TBD}}' for
when an issue arrived if you don't assign them immediately.
CREATE TABLE Users
(user_id INTEGER DEFAULT 0 PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL)
CREATE TABLE Assignments
(issue_nbr NOT NULL
REFERENCES Isuses (issue_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
user_id INTEGER DEFAULT '{{TBD}}' NOT NULL
REFERENCES Users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (issue_nbr, user_id)
assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
resolved_date DATETIME, -- null means still open);
CHECK (assigned_date <= resolved_date));
Now you have a whole tracking history.
SELECT @.my_date, I1.issue_nbr, I1.issue_description,
U1.user_id, U1.last_name, U1.first_name
FROM Issues AS I1, Assignments AS A1, Users AS U1
WHERE @.my_date BETWEEN A1.assigned_date AND A1.resolved_date
AND U1.user_id = A1.user_id
AND A1.issue_nbr = I1.issue_nbr;|||> So what is this magical "record_id" that appears
> to be everywhere?
> And why don' t you know that a row and record are totally different
> concepts? Why don't you use ISO-8601 Standard date formats? Why did
> you put the qualifier in the front of the names, in violation of the
> ISO-11179 rules for metadata?
> Why is there no resolution date in your issues? The model of time in
> SQL is durations, not single dates.
>
It appears that you missed the part where I said that I INHERITED this
structure. This is an application that the school district I work for
purchased and I have NO control over its structure. It is what it is. I
simply need to know if, given the structure that I laid out, is there a way
to return for each item in the ISSUES table beyond a parameterized date the
first and last name of the User last assigned to the Issue in the ASSIGN
table as well as all of the details of that Issue.
js|||You are screwed. Would you like an expert witness for the lawsuit?|||> You are screwed. Would you like an expert witness for the lawsuit?
;}
I guess I'll just write a stored procedure to move the records to a temp
table then and look up the User name against the Temp result set. Just was
looking for a quicker way.
js|||SELECT I.*, U.FirstName, U.LastName
FROM Issues I
INNER JOIN
(SELECT IDDefRec,
MAX(IDUser) As IDUser
FROM Assign
GROUP BY IDDefRec) A
ON I.IDRecord = A.IDDefRec
INNER JOIN USERS U
ON A.IDUser=U.IDRecord
--The above example just get the max of userid. To get the last assigned
userid, you have to add another column
in the ASSIGN table to keep track of the time of assignment.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeff Swanberg" <jswanberg@.swanbergcomputing.com> wrote in message
news:%23GMlmu$jFHA.3580@.TK2MSFTNGP09.phx.gbl...
> I'll simplify the table structure that I've inherited in order to try to
> explain what I need.
> Three tables - ISSUES, USERS and ASSIGN:
> ISSUES
> IDRecord - Primary Key
> Description
> DateEntered
> USERS
> IDRecord- Primary Key
> LastName
> FirstName
> ASSIGN
> IDRecord - Primary Key
> IDDefRec - matches to IDRecord in ISSUES
> IDUser - matches to IDRecord in USERS
>
> What I want is a result set for all ISSUES entered after 7/1/2005 (for
> example) that includes all of the columns from ISSUES and the FirstName
> and LastName of the last user assigned to the ISSUE. The ASSIGN table can
> contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
> So I figure I just need to get the TOP 1 of the ASSIGN table that matches
> the ISSUE and get the corresponding USER name. I just can't figure out
> how to do it in one SELECT statement.
> Jeff
>
>|||>> I'll just write a stored procedure to move the records [sic] to a temp table t
hen and look up the User name against the Temp result set. <<
That will not work. The schema does not show when someone was assigned
to an issue, only when the issue was first entered. Created on Monday,
assigned to Tom on Tuesday, handed off to Wendy on Wednesday and thrown
to Thomas on Thursday.
The design is flawed.|||> You are screwed. Would you like an expert witness for the lawsuit?
I'm not sure that someone that is mentally unstable would qualify as an expe
rt
witness. ;->
Thomas|||On Mon, 25 Jul 2005 09:03:17 -0700, "Thomas Coleman" <replyingroup@.anywhere.
com>
wrote:
in <eeuGRJTkFHA.1444@.TK2MSFTNGP10.phx.gbl>
>I'm not sure that someone that is mentally unstable would qualify as an exp
ert
>witness. ;->
>
>Thomas
I sure hope your insults are tongue in ch
because they have no place in a
professional newsgroup. Or are you NOT a professional?
Stefan Berglund|||> I sure hope your insults are tongue in ch because they have no place in ad">
> professional newsgroup. Or are you NOT a professional?
My sententious observations about Don Celko's behavior are as tongue and che
ek
as his remarks. ;->
Thomas
explain what I need.
Three tables - ISSUES, USERS and ASSIGN:
ISSUES
IDRecord - Primary Key
Description
DateEntered
USERS
IDRecord- Primary Key
LastName
FirstName
ASSIGN
IDRecord - Primary Key
IDDefRec - matches to IDRecord in ISSUES
IDUser - matches to IDRecord in USERS
What I want is a result set for all ISSUES entered after 7/1/2005 (for
example) that includes all of the columns from ISSUES and the FirstName and
LastName of the last user assigned to the ISSUE. The ASSIGN table can
contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
So I figure I just need to get the TOP 1 of the ASSIGN table that matches
the ISSUE and get the corresponding USER name. I just can't figure out how
to do it in one SELECT statement.
JeffWhat the first rule of a data model' A data element has one and only
one name in a schema. So what is this magical "record_id" that appears
to be everywhere?
And why don' t you know that a row and record are totally different
concepts? Why don't you use ISO-8601 Standard date formats? Why did
you put the qualifier in the front of the names, in violation of the
ISO-11179 rules for metadata?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
If you knew what a key was, followed ISO Standards, and underdstood DRI
action your non-existent DDL would look like this:
Why is there no resolution date in your issues? The model of time in
SQL is durations, not single dates.
CREATE TABLE Issues
(issue_nbr INTEGER NOT NULL PRIMARY KEY,
issue_description VARCHAR(255) NOT NULL);
Create a dummy user zero called "To Be Determined" or '{{TBD}}' for
when an issue arrived if you don't assign them immediately.
CREATE TABLE Users
(user_id INTEGER DEFAULT 0 PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL)
CREATE TABLE Assignments
(issue_nbr NOT NULL
REFERENCES Isuses (issue_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
user_id INTEGER DEFAULT '{{TBD}}' NOT NULL
REFERENCES Users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (issue_nbr, user_id)
assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
resolved_date DATETIME, -- null means still open);
CHECK (assigned_date <= resolved_date));
Now you have a whole tracking history.
SELECT @.my_date, I1.issue_nbr, I1.issue_description,
U1.user_id, U1.last_name, U1.first_name
FROM Issues AS I1, Assignments AS A1, Users AS U1
WHERE @.my_date BETWEEN A1.assigned_date AND A1.resolved_date
AND U1.user_id = A1.user_id
AND A1.issue_nbr = I1.issue_nbr;|||> So what is this magical "record_id" that appears
> to be everywhere?
> And why don' t you know that a row and record are totally different
> concepts? Why don't you use ISO-8601 Standard date formats? Why did
> you put the qualifier in the front of the names, in violation of the
> ISO-11179 rules for metadata?
> Why is there no resolution date in your issues? The model of time in
> SQL is durations, not single dates.
>
It appears that you missed the part where I said that I INHERITED this
structure. This is an application that the school district I work for
purchased and I have NO control over its structure. It is what it is. I
simply need to know if, given the structure that I laid out, is there a way
to return for each item in the ISSUES table beyond a parameterized date the
first and last name of the User last assigned to the Issue in the ASSIGN
table as well as all of the details of that Issue.
js|||You are screwed. Would you like an expert witness for the lawsuit?|||> You are screwed. Would you like an expert witness for the lawsuit?
;}
I guess I'll just write a stored procedure to move the records to a temp
table then and look up the User name against the Temp result set. Just was
looking for a quicker way.
js|||SELECT I.*, U.FirstName, U.LastName
FROM Issues I
INNER JOIN
(SELECT IDDefRec,
MAX(IDUser) As IDUser
FROM Assign
GROUP BY IDDefRec) A
ON I.IDRecord = A.IDDefRec
INNER JOIN USERS U
ON A.IDUser=U.IDRecord
--The above example just get the max of userid. To get the last assigned
userid, you have to add another column
in the ASSIGN table to keep track of the time of assignment.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeff Swanberg" <jswanberg@.swanbergcomputing.com> wrote in message
news:%23GMlmu$jFHA.3580@.TK2MSFTNGP09.phx.gbl...
> I'll simplify the table structure that I've inherited in order to try to
> explain what I need.
> Three tables - ISSUES, USERS and ASSIGN:
> ISSUES
> IDRecord - Primary Key
> Description
> DateEntered
> USERS
> IDRecord- Primary Key
> LastName
> FirstName
> ASSIGN
> IDRecord - Primary Key
> IDDefRec - matches to IDRecord in ISSUES
> IDUser - matches to IDRecord in USERS
>
> What I want is a result set for all ISSUES entered after 7/1/2005 (for
> example) that includes all of the columns from ISSUES and the FirstName
> and LastName of the last user assigned to the ISSUE. The ASSIGN table can
> contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
> So I figure I just need to get the TOP 1 of the ASSIGN table that matches
> the ISSUE and get the corresponding USER name. I just can't figure out
> how to do it in one SELECT statement.
> Jeff
>
>|||>> I'll just write a stored procedure to move the records [sic] to a temp table t
hen and look up the User name against the Temp result set. <<
That will not work. The schema does not show when someone was assigned
to an issue, only when the issue was first entered. Created on Monday,
assigned to Tom on Tuesday, handed off to Wendy on Wednesday and thrown
to Thomas on Thursday.
The design is flawed.|||> You are screwed. Would you like an expert witness for the lawsuit?
I'm not sure that someone that is mentally unstable would qualify as an expe
rt
witness. ;->
Thomas|||On Mon, 25 Jul 2005 09:03:17 -0700, "Thomas Coleman" <replyingroup@.anywhere.
com>
wrote:
in <eeuGRJTkFHA.1444@.TK2MSFTNGP10.phx.gbl>
>I'm not sure that someone that is mentally unstable would qualify as an exp
ert
>witness. ;->
>
>Thomas
I sure hope your insults are tongue in ch
because they have no place in aprofessional newsgroup. Or are you NOT a professional?
Stefan Berglund|||> I sure hope your insults are tongue in ch
because they have no place in ad">> professional newsgroup. Or are you NOT a professional?
My sententious observations about Don Celko's behavior are as tongue and che
ek
as his remarks. ;->
Thomas
Monday, March 19, 2012
Nested Query for newbie?
Hi
thanks for reading
I have 2 tables. The first has employee information and
the second has
payroll information. I need to find out people who are not
in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter
it and find
out for each payroll.
I don't think i have explained it very well so here is the
data set.
hope someone can help me with this.
Thanks in advance
prit
Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333
TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333
Required RESULT required(Missing employees for each pay
period)
IDNumSSN
1333333333
2111111111
If you have a table with all the IDNum values (IDNum represents a pay
period?), use it, or if not,
(select Distinct IDNum from TblPayrolldetail) IDs
where I have
IDs
select IDs.IDNum, [Tbl Employee] SSN
from IDs, [Tbl Employee]
where not exists (
select * from TblPayrolldetail D
where D.IDNum = IDs.IDNum
and D.SSN = [TblEmployee].SSN
)
Steve Kass
Drew University
PM wrote:
>Hi
>thanks for reading
>I have 2 tables. The first has employee information and
>the second has
>payroll information. I need to find out people who are not
>in the
>payroll but in the employee table.
>Since the payroll has multiple instances i have to filter
>it and find
>out for each payroll.
>I don't think i have explained it very well so here is the
>data set.
>hope someone can help me with this.
>Thanks in advance
>prit
>
>Tbl Employee
>PlanIDSSN
>1001111111111
>1001222222222
>1001333333333
>TblPayrolldetail
>IDNumPlanID SSN
>11001111111111
>11001222222222
>21001222222222
>21001333333333
>Required RESULT required(Missing employees for each pay
>period)
>IDNumSSN
>1333333333
>2111111111
>
>
thanks for reading
I have 2 tables. The first has employee information and
the second has
payroll information. I need to find out people who are not
in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter
it and find
out for each payroll.
I don't think i have explained it very well so here is the
data set.
hope someone can help me with this.
Thanks in advance
prit
Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333
TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333
Required RESULT required(Missing employees for each pay
period)
IDNumSSN
1333333333
2111111111
If you have a table with all the IDNum values (IDNum represents a pay
period?), use it, or if not,
(select Distinct IDNum from TblPayrolldetail) IDs
where I have
IDs
select IDs.IDNum, [Tbl Employee] SSN
from IDs, [Tbl Employee]
where not exists (
select * from TblPayrolldetail D
where D.IDNum = IDs.IDNum
and D.SSN = [TblEmployee].SSN
)
Steve Kass
Drew University
PM wrote:
>Hi
>thanks for reading
>I have 2 tables. The first has employee information and
>the second has
>payroll information. I need to find out people who are not
>in the
>payroll but in the employee table.
>Since the payroll has multiple instances i have to filter
>it and find
>out for each payroll.
>I don't think i have explained it very well so here is the
>data set.
>hope someone can help me with this.
>Thanks in advance
>prit
>
>Tbl Employee
>PlanIDSSN
>1001111111111
>1001222222222
>1001333333333
>TblPayrolldetail
>IDNumPlanID SSN
>11001111111111
>11001222222222
>21001222222222
>21001333333333
>Required RESULT required(Missing employees for each pay
>period)
>IDNumSSN
>1333333333
>2111111111
>
>
Nested Loops vs. Hash Match
I have two tables, Users and UserFile. Users.UserID is a PK, and
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Seeks on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index Seek on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Surely this can be done.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
JeradI believe I posted this in the wrong group, so I will repost in
microsoft.public.sqlserver.programming. Please respond on that board, to
prevent duplicate posts. Sorry for the cross posting.
Thanks.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>|||Hi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Seeks on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index Seek on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Surely this can be done.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
JeradI believe I posted this in the wrong group, so I will repost in
microsoft.public.sqlserver.programming. Please respond on that board, to
prevent duplicate posts. Sorry for the cross posting.
Thanks.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>|||Hi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
Nested Loops vs. Hash Match
I have two tables, Users and UserFile. Users.UserID is a PK, and
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Seeks on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index Seek on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Surely this can be done.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
JeradI believe I posted this in the wrong group, so I will repost in
microsoft.public.sqlserver.programming. Please respond on that board, to
prevent duplicate posts. Sorry for the cross posting.
Thanks.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>|||Hi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Seeks on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index Seek on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Surely this can be done.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
JeradI believe I posted this in the wrong group, so I will repost in
microsoft.public.sqlserver.programming. Please respond on that board, to
prevent duplicate posts. Sorry for the cross posting.
Thanks.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>|||Hi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
Nested Loops vs. Hash Match
I have two tables, Users and UserFile. Users.UserID is a PK, and
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Seeks on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index Seek on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Surely this can be done.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
Jerad
I believe I posted this in the wrong group, so I will repost in
microsoft.public.sqlserver.programming. Please respond on that board, to
prevent duplicate posts. Sorry for the cross posting.
Thanks.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
|||Hi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Seeks on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index Seek on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Surely this can be done.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
Jerad
I believe I posted this in the wrong group, so I will repost in
microsoft.public.sqlserver.programming. Please respond on that board, to
prevent duplicate posts. Sorry for the cross posting.
Thanks.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
|||Hi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:utuFQ9o4FHA.276@.TK2MSFTNGP09.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Seeks on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index Seek on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Surely this can be done.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>
Nested Loops vs. Hash Match
I have two tables, Users and UserFile. Users.UserID is a PK, and
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index Ss on both tables, with a
Nested Loops join joining the two tables. However, Query 2 results in an
Index S on Users, but an Index Scan on UserFile, as well as the more
costly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
The following gave me the desired execution plan and query cost (similar to
plan and cost of Query 1), but it uses a temp table which I don't want to
(and shouldn't have to) do:
declare @.Table table(UserFileID int)
insert into @.Table
select UserFile.UserFileID
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
select count(*)
from @.Table t
join UserFile
on UserFile.UserFileID = t.UserFileID
where UserFileBlockTrades = 0
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Basically, I'm just trying to force it to filter UserFileBlockTrades *after*
the join, instead of before. Surely this is possible.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
JeradHi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:eZzJ7Up4FHA.3588@.TK2MSFTNGP15.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index Ss on both tables, with a
> Nested Loops join joining the two tables. However, Query 2 results in an
> Index S on Users, but an Index Scan on UserFile, as well as the more
> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> The following gave me the desired execution plan and query cost (similar
> to plan and cost of Query 1), but it uses a temp table which I don't want
> to (and shouldn't have to) do:
> declare @.Table table(UserFileID int)
> insert into @.Table
> select UserFile.UserFileID
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> select count(*)
> from @.Table t
> join UserFile
> on UserFile.UserFileID = t.UserFileID
> where UserFileBlockTrades = 0
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Basically, I'm just trying to force it to filter UserFileBlockTrades
> *after* the join, instead of before. Surely this is possible.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>|||Oh wow, you're exactly right Greg. I didn't have an index set up for
UserFileBlockTrades, and once I added it, both queries generated the exact
same plan.
Here's my question now (just so I understand this). The reason I didn't
have an index on UserFileBlockTrades in the first place, is because it is a
bit field, and the selectivity on it is extremely low (as I said in my
original post). So I thought SQL would ignore this index anyway.
So can you help me understand why this made a difference (why SQL did, in
fact, use my index), even considering the low selectivity of that filter? I
generally (if not always) avoid indexing my bit fields for this reason, but
now I see it is needed in some cases.
Thanks so much for your help.
Jerad
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OqgdXWp4FHA.2060@.TK2MSFTNGP09.phx.gbl...
> Hi Jerad
> The answer to this most likely lies in indexing, not re-structuring the
> query. What indexes are available on these tables? Running sp_helpindex
> [tablename] against both table would give us more of a full picture.
> As a started, I'd suggest that the following two indexes should be there,
> but the recommendation could change depending on whether either table has
> clustered indexes & on which columns..
> Users (UserLastActive, UserID)
> UserFile (UserID, UserFileBlockTrades)
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jerad Rose" <no@.spam.com> wrote in message
> news:eZzJ7Up4FHA.3588@.TK2MSFTNGP15.phx.gbl...
>|||Seems like the optimiser found that bit column helpful in this case, but
keep in mind that including columns in indexes isn't always about
selectivity & row identification. It usually helps to have all columns
included in indexes in situations like this (whether in the WHERE, JOIN or
SELECT part of query), where not too many columns are involved in the query.
This allows SQL Server to find all the information in indexes, without
having to go back to the underlying table structures. This is important
because indexes are packed far more densely than tables (many more rows per
page) & can therefore usually give far higher performance.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:OtQo5fp4FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Oh wow, you're exactly right Greg. I didn't have an index set up for
> UserFileBlockTrades, and once I added it, both queries generated the exact
> same plan.
> Here's my question now (just so I understand this). The reason I didn't
> have an index on UserFileBlockTrades in the first place, is because it is
> a bit field, and the selectivity on it is extremely low (as I said in my
> original post). So I thought SQL would ignore this index anyway.
> So can you help me understand why this made a difference (why SQL did, in
> fact, use my index), even considering the low selectivity of that filter?
> I generally (if not always) avoid indexing my bit fields for this reason,
> but now I see it is needed in some cases.
> Thanks so much for your help.
> Jerad
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OqgdXWp4FHA.2060@.TK2MSFTNGP09.phx.gbl...
>|||On Sun, 6 Nov 2005 01:04:53 -0500, Jerad Rose wrote:
>So can you help me understand why this made a difference (why SQL did, in
>fact, use my index), even considering the low selectivity of that filter?
I
>generally (if not always) avoid indexing my bit fields for this reason, but
>now I see it is needed in some cases.
Hi Jerad,
You didn't mention whether the new index was actually used in the
execution plans. Since you're saying that both queries are now using the
exact same plan, I suspect that the index was NOT used.
My assumption on why the index made a difference - I guess that there
were no statistics yet for the UserFileBlockTrades column. Since it's a
bit column, the optimizer would probably have estimated a selectivity of
50%. After adding the index, there were statistics; the optimizer now
knows that the selectivity will actually be 96% and decides on another
plan.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Jerad Rose (no@.spam.com) writes:
> Oh wow, you're exactly right Greg. I didn't have an index set up for
> UserFileBlockTrades, and once I added it, both queries generated the exact
> same plan.
I think Hugo's remark about statistics is right on the money, but as an
addendum, permit me to point out a gotcha with index on bit columns. This
was the query:
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
Say now that UserFileBlockTrades = 0 would be very selective, and give a
mere handful of rows. Would the index be used now? Probably not, because
you need to do this:
and UserFile.UserFileBlockTrades = convert(bit, 0)
this is because of the rules for implicit conversion in SQL Server, which
says that these are always performed according to a data-type precendence
order (which is in Books Online). And bit is converted to integer, and
0 is an integer.
I'm adding this, because I did exactly this mistake recently when I added
indexes on some bit columns, and then was surprised that the performance
boost was nowhere near to what I expected it to be.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hugo's point about statistics is probably relevant, but the fact that the
new index/s precisely cover the query probably has more to do with the
consistency in the plans because the optimiser will take their i/o
efficiency into account. Obviously its better to pull the
UserFileBlockTrades value out off the covering index than do a bookmark or
heap lookup to get the value during a s.
Of course it would far clearer if Jerad would post the table structures,
index structures & execution plans.
Regards,
Greg Linwood
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970679CB84306Yazorman@.127.0.0.1...
> Jerad Rose (no@.spam.com) writes:
> I think Hugo's remark about statistics is right on the money, but as an
> addendum, permit me to point out a gotcha with index on bit columns. This
> was the query:
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> Say now that UserFileBlockTrades = 0 would be very selective, and give a
> mere handful of rows. Would the index be used now? Probably not, because
> you need to do this:
> and UserFile.UserFileBlockTrades = convert(bit, 0)
> this is because of the rules for implicit conversion in SQL Server, which
> says that these are always performed according to a data-type precendence
> order (which is in Books Online). And bit is converted to integer, and
> 0 is an integer.
> I'm adding this, because I did exactly this mistake recently when I added
> indexes on some bit columns, and then was surprised that the performance
> boost was nowhere near to what I expected it to be.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||On Sun, 6 Nov 2005 22:24:47 +1100, Greg Linwood wrote:
>Hugo's point about statistics is probably relevant, but the fact that the
>new index/s precisely cover the query probably has more to do with the
>consistency in the plans because the optimiser will take their i/o
>efficiency into account. Obviously its better to pull the
>UserFileBlockTrades value out off the covering index than do a bookmark or
>heap lookup to get the value during a s.
Hi Greg,
Ah, I now see that I've been guilty of sloppy reading. I didn't check
the indexes you suggested carefully enough; I missed that you included
the joining column as well as the searched column in the index.
That brings me on yet another theory: maybe the optimizer decided to use
the new index because there was at first not a single index on the
joining column. That could be verified by changing the index to include
only the joining column.
>Of course it would far clearer if Jerad would post the table structures,
>index structures & execution plans.
Yes, indeed!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The new theory is also probably correct as well, but I wasn't what indexes
he had on those tables, so I just suggested new ones.
If Jerad sends through his existing indexes & the showplan, it'd make things
nice & clear! (c:
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:im0sm156ei3bau989tf0o7vi8mn2t4blaf@.
4ax.com...
> On Sun, 6 Nov 2005 22:24:47 +1100, Greg Linwood wrote:
>
> Hi Greg,
> Ah, I now see that I've been guilty of sloppy reading. I didn't check
> the indexes you suggested carefully enough; I missed that you included
> the joining column as well as the searched column in the index.
> That brings me on yet another theory: maybe the optimizer decided to use
> the new index because there was at first not a single index on the
> joining column. That could be verified by changing the index to include
> only the joining column.
>
> Yes, indeed!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
UserFile.UserID is a FK to Users. The relationship between Users and
UserFile is one-to-many.
I have the following two queries:
--Query 1
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
--Query 2
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
The only difference is that the last line of Query 2 is omitted in Query 1.
The selectivity of the Users filter is quite high -- about 6%. The
selectivity of the UserFile filter is quite low -- about 96%.
Here's the problem. Query 1 results in Index S
s on both tables, with aNested Loops join joining the two tables. However, Query 2 results in an
Index S
on Users, but an Index Scan on UserFile, as well as the morecostly Hash Match join joining the two tables. The result is, Query 2 has
about 4x CPU cost of Query 1.
Here's my theory.
Query 1 is saying (in English), take all Users (55,100), filter by
UserLastActive (3,226), and join the remaining records on UserFile (8,307).
Query 2 is saying, take all Users (55,100), filter by UserLastActive
(3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
(65,814), and join the two results (7,520).
Running a filter (UserFileBlockTrades) with such a low selectivity on such a
large set of records is obviously going to be costly. So, my question is,
is there a way to restructure my query so that SQL will do this instead:
Take all Users (55,100), filter by UserLastActive (3,226), join the
remaining records on UserFile (8,307), and filter by UserFileBlockTrades
(7,520).
The selectivity of the UserFileBlockTrades filter will still be low, but it
will be dealing with a much smaller set of data. I don't know if this is
possible, but I would think it would be, considering the fact that this
seems like something that would happen quite a bit.
The following gave me the desired execution plan and query cost (similar to
plan and cost of Query 1), but it uses a temp table which I don't want to
(and shouldn't have to) do:
declare @.Table table(UserFileID int)
insert into @.Table
select UserFile.UserFileID
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
select count(*)
from @.Table t
join UserFile
on UserFile.UserFileID = t.UserFileID
where UserFileBlockTrades = 0
I tried to use a derived table to force the low selectivity filter after
everything else has been done:
select count(*)
from (
select UserFile.*
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
) UserFile
where UserFile.UserFileBlockTrades = 0
But the execution plan was exactly the same as the one from Query 2, so this
had zero effect.
Basically, I'm just trying to force it to filter UserFileBlockTrades *after*
the join, instead of before. Surely this is possible.
Any ideas or suggestions? Let me know if you need any other info from me.
Thanks in advance for your help.
JeradHi Jerad
The answer to this most likely lies in indexing, not re-structuring the
query. What indexes are available on these tables? Running sp_helpindex
[tablename] against both table would give us more of a full picture.
As a started, I'd suggest that the following two indexes should be there,
but the recommendation could change depending on whether either table has
clustered indexes & on which columns..
Users (UserLastActive, UserID)
UserFile (UserID, UserFileBlockTrades)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:eZzJ7Up4FHA.3588@.TK2MSFTNGP15.phx.gbl...
>I have two tables, Users and UserFile. Users.UserID is a PK, and
>UserFile.UserID is a FK to Users. The relationship between Users and
>UserFile is one-to-many.
> I have the following two queries:
> --Query 1
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> --Query 2
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> The only difference is that the last line of Query 2 is omitted in Query
> 1. The selectivity of the Users filter is quite high -- about 6%. The
> selectivity of the UserFile filter is quite low -- about 96%.
> Here's the problem. Query 1 results in Index S
s on both tables, with a> Nested Loops join joining the two tables. However, Query 2 results in an
> Index S
on Users, but an Index Scan on UserFile, as well as the more> costly Hash Match join joining the two tables. The result is, Query 2 has
> about 4x CPU cost of Query 1.
> Here's my theory.
> Query 1 is saying (in English), take all Users (55,100), filter by
> UserLastActive (3,226), and join the remaining records on UserFile
> (8,307).
> Query 2 is saying, take all Users (55,100), filter by UserLastActive
> (3,226), then take all UserFiles (68,617), filter by UserFileBlockTrades
> (65,814), and join the two results (7,520).
> Running a filter (UserFileBlockTrades) with such a low selectivity on such
> a large set of records is obviously going to be costly. So, my question
> is, is there a way to restructure my query so that SQL will do this
> instead:
> Take all Users (55,100), filter by UserLastActive (3,226), join the
> remaining records on UserFile (8,307), and filter by UserFileBlockTrades
> (7,520).
> The selectivity of the UserFileBlockTrades filter will still be low, but
> it will be dealing with a much smaller set of data. I don't know if this
> is possible, but I would think it would be, considering the fact that this
> seems like something that would happen quite a bit.
> The following gave me the desired execution plan and query cost (similar
> to plan and cost of Query 1), but it uses a temp table which I don't want
> to (and shouldn't have to) do:
> declare @.Table table(UserFileID int)
> insert into @.Table
> select UserFile.UserFileID
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> select count(*)
> from @.Table t
> join UserFile
> on UserFile.UserFileID = t.UserFileID
> where UserFileBlockTrades = 0
> I tried to use a derived table to force the low selectivity filter after
> everything else has been done:
> select count(*)
> from (
> select UserFile.*
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> ) UserFile
> where UserFile.UserFileBlockTrades = 0
> But the execution plan was exactly the same as the one from Query 2, so
> this had zero effect.
> Basically, I'm just trying to force it to filter UserFileBlockTrades
> *after* the join, instead of before. Surely this is possible.
> Any ideas or suggestions? Let me know if you need any other info from me.
> Thanks in advance for your help.
> Jerad
>|||Oh wow, you're exactly right Greg. I didn't have an index set up for
UserFileBlockTrades, and once I added it, both queries generated the exact
same plan.
Here's my question now (just so I understand this). The reason I didn't
have an index on UserFileBlockTrades in the first place, is because it is a
bit field, and the selectivity on it is extremely low (as I said in my
original post). So I thought SQL would ignore this index anyway.
So can you help me understand why this made a difference (why SQL did, in
fact, use my index), even considering the low selectivity of that filter? I
generally (if not always) avoid indexing my bit fields for this reason, but
now I see it is needed in some cases.
Thanks so much for your help.
Jerad
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OqgdXWp4FHA.2060@.TK2MSFTNGP09.phx.gbl...
> Hi Jerad
> The answer to this most likely lies in indexing, not re-structuring the
> query. What indexes are available on these tables? Running sp_helpindex
> [tablename] against both table would give us more of a full picture.
> As a started, I'd suggest that the following two indexes should be there,
> but the recommendation could change depending on whether either table has
> clustered indexes & on which columns..
> Users (UserLastActive, UserID)
> UserFile (UserID, UserFileBlockTrades)
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jerad Rose" <no@.spam.com> wrote in message
> news:eZzJ7Up4FHA.3588@.TK2MSFTNGP15.phx.gbl...
>|||Seems like the optimiser found that bit column helpful in this case, but
keep in mind that including columns in indexes isn't always about
selectivity & row identification. It usually helps to have all columns
included in indexes in situations like this (whether in the WHERE, JOIN or
SELECT part of query), where not too many columns are involved in the query.
This allows SQL Server to find all the information in indexes, without
having to go back to the underlying table structures. This is important
because indexes are packed far more densely than tables (many more rows per
page) & can therefore usually give far higher performance.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jerad Rose" <no@.spam.com> wrote in message
news:OtQo5fp4FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Oh wow, you're exactly right Greg. I didn't have an index set up for
> UserFileBlockTrades, and once I added it, both queries generated the exact
> same plan.
> Here's my question now (just so I understand this). The reason I didn't
> have an index on UserFileBlockTrades in the first place, is because it is
> a bit field, and the selectivity on it is extremely low (as I said in my
> original post). So I thought SQL would ignore this index anyway.
> So can you help me understand why this made a difference (why SQL did, in
> fact, use my index), even considering the low selectivity of that filter?
> I generally (if not always) avoid indexing my bit fields for this reason,
> but now I see it is needed in some cases.
> Thanks so much for your help.
> Jerad
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OqgdXWp4FHA.2060@.TK2MSFTNGP09.phx.gbl...
>|||On Sun, 6 Nov 2005 01:04:53 -0500, Jerad Rose wrote:
>So can you help me understand why this made a difference (why SQL did, in
>fact, use my index), even considering the low selectivity of that filter?
I
>generally (if not always) avoid indexing my bit fields for this reason, but
>now I see it is needed in some cases.
Hi Jerad,
You didn't mention whether the new index was actually used in the
execution plans. Since you're saying that both queries are now using the
exact same plan, I suspect that the index was NOT used.
My assumption on why the index made a difference - I guess that there
were no statistics yet for the UserFileBlockTrades column. Since it's a
bit column, the optimizer would probably have estimated a selectivity of
50%. After adding the index, there were statistics; the optimizer now
knows that the selectivity will actually be 96% and decides on another
plan.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Jerad Rose (no@.spam.com) writes:
> Oh wow, you're exactly right Greg. I didn't have an index set up for
> UserFileBlockTrades, and once I added it, both queries generated the exact
> same plan.
I think Hugo's remark about statistics is right on the money, but as an
addendum, permit me to point out a gotcha with index on bit columns. This
was the query:
select count(*)
from Users
join UserFile
on UserFile.UserID = Users.UserID
where Users.UserLastActive > getdate() - 14
and UserFile.UserFileBlockTrades = 0
Say now that UserFileBlockTrades = 0 would be very selective, and give a
mere handful of rows. Would the index be used now? Probably not, because
you need to do this:
and UserFile.UserFileBlockTrades = convert(bit, 0)
this is because of the rules for implicit conversion in SQL Server, which
says that these are always performed according to a data-type precendence
order (which is in Books Online). And bit is converted to integer, and
0 is an integer.
I'm adding this, because I did exactly this mistake recently when I added
indexes on some bit columns, and then was surprised that the performance
boost was nowhere near to what I expected it to be.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hugo's point about statistics is probably relevant, but the fact that the
new index/s precisely cover the query probably has more to do with the
consistency in the plans because the optimiser will take their i/o
efficiency into account. Obviously its better to pull the
UserFileBlockTrades value out off the covering index than do a bookmark or
heap lookup to get the value during a s
.Of course it would far clearer if Jerad would post the table structures,
index structures & execution plans.
Regards,
Greg Linwood
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970679CB84306Yazorman@.127.0.0.1...
> Jerad Rose (no@.spam.com) writes:
> I think Hugo's remark about statistics is right on the money, but as an
> addendum, permit me to point out a gotcha with index on bit columns. This
> was the query:
> select count(*)
> from Users
> join UserFile
> on UserFile.UserID = Users.UserID
> where Users.UserLastActive > getdate() - 14
> and UserFile.UserFileBlockTrades = 0
> Say now that UserFileBlockTrades = 0 would be very selective, and give a
> mere handful of rows. Would the index be used now? Probably not, because
> you need to do this:
> and UserFile.UserFileBlockTrades = convert(bit, 0)
> this is because of the rules for implicit conversion in SQL Server, which
> says that these are always performed according to a data-type precendence
> order (which is in Books Online). And bit is converted to integer, and
> 0 is an integer.
> I'm adding this, because I did exactly this mistake recently when I added
> indexes on some bit columns, and then was surprised that the performance
> boost was nowhere near to what I expected it to be.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||On Sun, 6 Nov 2005 22:24:47 +1100, Greg Linwood wrote:
>Hugo's point about statistics is probably relevant, but the fact that the
>new index/s precisely cover the query probably has more to do with the
>consistency in the plans because the optimiser will take their i/o
>efficiency into account. Obviously its better to pull the
>UserFileBlockTrades value out off the covering index than do a bookmark or
>heap lookup to get the value during a s
.Hi Greg,
Ah, I now see that I've been guilty of sloppy reading. I didn't check
the indexes you suggested carefully enough; I missed that you included
the joining column as well as the searched column in the index.
That brings me on yet another theory: maybe the optimizer decided to use
the new index because there was at first not a single index on the
joining column. That could be verified by changing the index to include
only the joining column.
>Of course it would far clearer if Jerad would post the table structures,
>index structures & execution plans.
Yes, indeed!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The new theory is also probably correct as well, but I wasn't what indexes
he had on those tables, so I just suggested new ones.
If Jerad sends through his existing indexes & the showplan, it'd make things
nice & clear! (c:
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:im0sm156ei3bau989tf0o7vi8mn2t4blaf@.
4ax.com...
> On Sun, 6 Nov 2005 22:24:47 +1100, Greg Linwood wrote:
>
> Hi Greg,
> Ah, I now see that I've been guilty of sloppy reading. I didn't check
> the indexes you suggested carefully enough; I missed that you included
> the joining column as well as the searched column in the index.
> That brings me on yet another theory: maybe the optimizer decided to use
> the new index because there was at first not a single index on the
> joining column. That could be verified by changing the index to include
> only the joining column.
>
> Yes, indeed!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)