April 18, 2012 at 1:17 pm
We have an application that enables our users to run adhoc selections over tables and display the results in SSRS reports. In simple terms it creates a temporary SQL view using a primary table and adding in the joins to look up tables as needed to supply the required data or support the selection WHERE clause. Some of these selections take minutes to run, while running only the SQL statement used to create the temporary views takes seconds. So we have some performance issue(s).
One of our developers has stated that the performance issues are all because it uses SQL views and these are inefficient. What we should be doing is creating a temporary table with a structure to accommodate all the columns required, then insert all the records from the primary table, then update these records from each of the join tables in turn, so several update statements, then drop all records that do not match the WHERE clause. He says that he has proven that this more efficient than using SQL views.
My opinion is that this can only be more efficient if the view has been build wrong such that the joins result in a 1 to many relationship from the primary table.
Are SQL views efficient compared to this table insert and update method?
April 18, 2012 at 1:27 pm
Depends on the complexity of the view. If it's view upon view upon view it's probably inefficient. If it's a single view that's not particuarly complex, there's probably little to no performance impact.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2012 at 1:35 pm
ill also say that if the tables in the view have indexes specially designed for the view it can be very fast.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 1:38 pm
"We have an application that enables our users to run adhoc selections over tables and display the results in SSRS reports. In simple terms it creates a temporary SQL view using a primary table and adding in the joins to look up tables as needed to supply the required data or support the selection WHERE clause. Some of these selections take minutes to run, while running only the SQL statement used to create the temporary views takes seconds. So we have some performance issue(s). "
What exactly is the process here? The users are selecting tables at random ( from a combo box?) apart from the Primary table, this is then joined to the primary table, then an SSRS report is defined/used to display the results. Is that a stored proc which produces the view ( lots of if, case statements to generate, cursor?) then uses presumably a matrix to dynamically create SSRS columns from columns chosen by the user?
I would like to know more details about the production process of the view and the report, as this seems a much more likely cause as you say the view runs within seconds.
Fitz
April 18, 2012 at 1:49 pm
Thanks, all the joins are table to table, the only view involved is the temporary view that pulls together all the table joins so that the SSRS can simple read a standard layout to print from.
There is a complexity though in that all the tables are monthly tables partitioned by the day of the month. This means that the WHERE clause includes criteria to select only the day of the month required from each of the tables. So if we are interested in data for April 18 we select where the partition is 18.
How should the WHERE be ordered to ensure that only data from the 18 partition on the primary table is selected so all joins are only on data from the primary table 18 partition and not across all partitions and then the 18 partition selected from the massive record set of all partition joins (there are indexes on all the tables on the partition column)?
So given 3 tables a, b,c where a is the primary table should the WHERE be:
> a.partition = 18 and b.partition = 18 and c.partition = 18
or
> a.partition = 18 and a.partition = b.partition and a.partition = c.partition
or another variation?
April 18, 2012 at 1:53 pm
There are indexes on all the columns used in the JOINs but not on all the columns that could be in the WHERE clause (otherwise we would have indexes on every column on the tables)
April 18, 2012 at 2:09 pm
Mark Fitzgerald-331224 (4/18/2012)
What exactly is the process here? The users are selecting tables at random ( from a combo box?) apart from the Primary table, this is then joined to the primary table, then an SSRS report is defined/used to display the results. Is that a stored proc which produces the view ( lots of if, case statements to generate, cursor?) then uses presumably a matrix to dynamically create SSRS columns from columns chosen by the user?I would like to know more details about the production process of the view and the report, as this seems a much more likely cause as you say the view runs within seconds.
Fitz
The primary table and the possible secondary tables and the joins are held in configuration tables. The user runs a webpage and then chooses to run a selection over a particular 'view' (not SQL view) of the data, not the tables directly. If they select columns for data or subset the data from a secondary table then that table is included in the SQL statement to create the temporary view, if any of the secondary tables are not required then they are not included in the view.
The view is created by calling a stored procedure (so it runs on the server) and is passed the CREATE VIEW SQL statement.
The SSRS is then run passing the name of the temporary view which references standard names so it reads field1, field2, field3 etc. and places field1, field2, field3 in set positions.
So the user selects columns a, b, c and these are mapped as field1, field2, field3 on the report. They can then run a different selection using columns x, y, z and these are mapped as field1, field2, field3 on the report.
April 18, 2012 at 2:13 pm
Mark Fitzgerald-331224 (4/18/2012)
"We have an application that enables our users to run adhoc selections over tables and display the results in SSRS reports. In simple terms it creates a temporary SQL view using a primary table and adding in the joins to look up tables as needed to supply the required data or support the selection WHERE clause. Some of these selections take minutes to run, while running only the SQL statement used to create the temporary views takes seconds. So we have some performance issue(s). "What exactly is the process here? The users are selecting tables at random ( from a combo box?) apart from the Primary table, this is then joined to the primary table, then an SSRS report is defined/used to display the results. Is that a stored proc which produces the view ( lots of if, case statements to generate, cursor?) then uses presumably a matrix to dynamically create SSRS columns from columns chosen by the user?
I would like to know more details about the production process of the view and the report, as this seems a much more likely cause as you say the view runs within seconds.
Fitz
Same questions still apply. You mention that lookup tables are added to be used in the report and the where clause.
1: how are the lookup tables added to the view
2: how do the joins get added to the view
3: how are the where clauses generated for the view
4: although you say you have a standard layout, how does this deal with the adhoc columns that are chosen by the user
5: is the SSRS report layout generated on the fly as well as the view to incorporate a standard header/footer with a changeable report body
Fitz
Sorry entries were added at te same time.
April 18, 2012 at 2:55 pm
pwallis (4/18/2012)
Mark Fitzgerald-331224 (4/18/2012)
What exactly is the process here? The users are selecting tables at random ( from a combo box?) apart from the Primary table, this is then joined to the primary table, then an SSRS report is defined/used to display the results. Is that a stored proc which produces the view ( lots of if, case statements to generate, cursor?) then uses presumably a matrix to dynamically create SSRS columns from columns chosen by the user?I would like to know more details about the production process of the view and the report, as this seems a much more likely cause as you say the view runs within seconds.
Fitz
The primary table and the possible secondary tables and the joins are held in configuration tables. The user runs a webpage and then chooses to run a selection over a particular 'view' (not SQL view) of the data, not the tables directly. If they select columns for data or subset the data from a secondary table then that table is included in the SQL statement to create the temporary view, if any of the secondary tables are not required then they are not included in the view.
The view is created by calling a stored procedure (so it runs on the server) and is passed the CREATE VIEW SQL statement.
The SSRS is then run passing the name of the temporary view which references standard names so it reads field1, field2, field3 etc. and places field1, field2, field3 in set positions.
So the user selects columns a, b, c and these are mapped as field1, field2, field3 on the report. They can then run a different selection using columns x, y, z and these are mapped as field1, field2, field3 on the report.
The generation of the view tsql statement is dynamic with zero or more of the secondary tables being used to either include columns for output or use in a where clause. Depending upon the number of such tables available and the technique used to create the statement, then this may take a lot of time. As you also say the primary table changes dependent upon the month then this also increases the time.
Have you tried to evaluate the time taken by each stage in the production (generate the view, run the view and generate the report). Not knowing your structures but from your description I would expected the create view to be the least likely to cause the issue.
Fitz
April 18, 2012 at 4:56 pm
pwallis (4/18/2012)
We have an application that enables our users to run adhoc selections over tables and display the results in SSRS reports. In simple terms it creates a temporary SQL view using a primary table and adding in the joins to look up tables as needed to supply the required data or support the selection WHERE clause. Some of these selections take minutes to run, while running only the SQL statement used to create the temporary views takes seconds. So we have some performance issue(s).
Asking if SQL Views are efficient is a lot like asking if SQL Queries are efficient. They certainly can be. At the same time, I have certainly seen plenty of inefficient queries (and views).
One thing to look out for is what Gail Shaw mentioned: views referencing other views. Is it possible for your users to select columns from permanent views on the database and include them in the temporary views they are creating? That could create real problems.
Here is the basic approach I would take:
1. Figure out what part of your process is taking all of the time. Is it parsing the user request and creating the ad hoc SQL code? Is it the SQL query execution itself? Is it the report rendering in SSRS?
2. If SQL Query Execution is a performance problem, then the next step is to find out what SQL code is actually being executed. (Your application should log the SQL queries it executes. You can also try to capture this at run time using SQL Profiler or a server-side trace.)
3. Once you have isolated a problem query, try running that query in SSMS outside your application. What is performance like then? Get the execution plans for these queries. Hopefully you have someone on staff who can do a little performance analysis for you.
Good Luck!
April 18, 2012 at 5:05 pm
The problem with VIEWs can simply be that they are necessarily "all in one query". I've changed many a query from takin minutes to execute to subsecond and small second times by using the "Divide'n'Conquer" methods your Developer spoke of using temporary tables to quickly isolate very small interim result sets and then joining those together instead of larger tables. This is especially true for queries that use DISTINCT and/or GROUP BY which tends to mask hidden "many to many" joins.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2012 at 12:40 am
Thanks to everyone. Looks like the consensus of view vs table, is 'it depends' 🙂 and we are going to have to trace where the bottleneck is rather than simply alter the method.
April 19, 2012 at 7:18 am
pwallis (4/19/2012)
...we are going to have to trace where the bottleneck is rather than simply alter the method.
For some reason, I thought you had already done that. 😉 THAT should always be the first step before making any change for the sake of performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2012 at 7:46 am
The application can be run in a test mode to check the results are as required before running in production mode where the results are processed by the SSRS and we can view the SQL statement that is used.
The developer is indicating that it is in executing the CREATE VIEW using the SQL statement that takes the time, if we run just the SQL statement without the CREATE VIEW around it then it doesn't take minutes for the SQL statement to be executed.
So the proposal was remove the CREATE VIEW and use the table method 'as SQL views are not efficient'. Given the consensus that SQL Views are not inefficient if they are built over tables and the joins have indexes etc. then I think we need more tangible evidence that it is the CREATE VIEW at fault and not to simply rewrite the method because it uses a view. Besides it isn't all selections that have the issue.
Will the order of the clauses in the WHERE clause say result in all records from the primary table being read and then the joins occur and then the records filtered back, instead of subsetting the primary records 1st and then applying the joins? (This goes back the prior question about the use of partitions).
April 19, 2012 at 7:51 am
pwallis (4/19/2012)
The developer is indicating that it is in executing the CREATE VIEW using the SQL statement that takes the time, if we run just the SQL statement without the CREATE VIEW around it then it doesn't take minutes for the SQL statement to be executed.
A Create View takes no time. SQL doesn't execute the query, it just parses it, then saves the view in the system catalog. The only way that could be slow is via blocking, then you'd have to find out what's blocking it and why
A SELECT * FROM <view> is exactly the same as running the query that defines the view. All a view is is a saved select statement.
Will the order of the clauses in the WHERE clause say result in all records from the primary table being read and then the joins occur and then the records filtered back, instead of subsetting the primary records 1st and then applying the joins? (This goes back the prior question about the use of partitions).
No. The optimiser is smarter than that. Order that the where clause predicates or joins are specified in the query does not determine the order they are executed in.
It sounds to me like the dev is trying to feed you a lot of manure (or the dev is clueless, can't tell)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply