July 12, 2011 at 6:36 pm
In the context of the below code, the issue I'm having is this: I used to be able take and create a 'calculated table', wherein I'd dump the contents of both Sales and Nonsales into one table (analogous to the contents of tblPerson), and query against that table, which performed fine.
However, due to some business changes, I now have to replace that table with a Union View ... which will be named the same as my calculated table before, and used in thousands of procs, many of which are hairy and slow to begin with.
So here is my question: What's the best way to compose the view tblPerson, and what indexes are recommended? I have flexibility in general, but need the output to remain the same, because as I say, it's replacing a table.
drop table sales;
drop table nonsales;
drop table offices
;
create table offices
(
office_id int identity primary key
,officename varchar(20)
);
create table sales
(
sales_id int identity primary key
,office_id int references offices(office_id)
,printedname varchar(20)
);
create table nonsales
(
nonsales_id int identity primary key
,office_id int references offices(office_id)
,printedname varchar(20)
);
insert offices (officename) select 'HQ' union select 'Branch1' union select 'Branch2';
insert sales (office_id, printedname) select '1', 'President Bob'
insert sales (office_id, printedname) select '1', 'Sales VP Joe'
insert sales (office_id, printedname) select '2', 'Salesman Bill'
insert sales (office_id, printedname) select '2', 'Salesman Steve'
insert sales (office_id, printedname) select '3', 'Salesman John'
insert sales (office_id, printedname) select '3', 'Salesman Fred'
insert nonsales (office_id, printedname) select '1', 'Admin Susie'
insert nonsales (office_id, printedname) select '2', 'Admin Todd'
insert nonsales (office_id, printedname) select '3', 'Admin Chris'
if object_id('tblPerson') is not null drop view tblPerson
;
create view tblPerson as
select * from sales
union all
select * from nonsales
;
This is an example query I'd be likely to run, which is now (often) much slower than it was when sales/nonsales were pre-compiled into a single table.
QUERY 1
-- check this in the Analyzer to see the CI Scans on Sales/Nonsales ... I think those are what's making my queries slower
select * from offices o
join tblPerson p
on o.office_id = p.office_id
where p.office_id = 1
Any thoughts/advice/tricks to make tblPersons work as fast as possible when I join to it, aside from the obvious of indexing fields I'm likely to join or do lookups against?
TIA:-D
July 13, 2011 at 1:36 am
Please post your execution plans. It's impossible to get meaningful performance results on 10 rows
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
July 13, 2011 at 2:44 pm
Thanks GM.
Please check my edits: I've made my question a lot simpler
What I'm really wondering is ... are there any changes you'd recommend, either to the way tblPerson view is written, or indexing, given that I'm going to be replacing a Table (tblPerson) with a Union View (tblPerson) in the way I'm describing?
I'm going to be joining to this view very regularly, and needing to pull 5-10 fields from it into a result set. Typically, sales has about 100K rows, and nonsales about 10K rows. My result sets could involve anything between pulling a single row for a single person to returning all persons for an entire region (up to about 10K persons).
Such queries used to fly when both Sales and Nonsales were shoved together into a table ... but now, using my 'on the fly' Union View, I'm seeing slower performance on a lot of queries.
So ... any tips/tricks to make a Union query like this work as fast as possible are appreciated :w00t:
July 28, 2011 at 1:28 pm
Some thoughts on how you will get performance close to what you had with a concrete version of tblPersons:
1) the indexes on sales and nonsales must exactly match each other, as well as match the indexes of the concrete table tblPersons
2) you persist your UNION ALL view as an indexed view to replace concrete table tblPersons, and to index the view the same exact way as tblPersons *
Re: #2 what Edition of SQL Server are you running: Standard, Enterprise, a specialized edition?
* there are some restrictions on when you can use indexed views and the schema of sales or nonsales may disqualify you from using them
Please post the information Gail requested. DDL complete with indexes and constraints for all involved tables would also be helpful.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply