|
|
You're in the
sql server programming
group:Indexed View
sql server programming:
[quoted text, click to view]
>> When the users place orders, the customerid and employeeid are optional. <<Doesn't that seem to be strange business practice? Where do you sent all these "mystery orders" since you have no idea who paid for them? If employees enter orders, how do they log on? Or is there a general "Embezzler" password that employees who want to ship those orders to their friends can use? [quoted text, click to view] >> I'm going to insert a row in customers table for unregistered customers and a row into employees table for orders inserted withoutemployeeid << Then, based on the DDL that you have never shown us, registration is an attribute of a customer? So I can buy stuff from you BEFORE you find out that I am terrorist stolen credit cards? I can see moving some orders to a "pending" status and creating a new customer when you take the order, but there is no excuse for not requiring an employee id. Well, if you are really trying to set up the system to invite embezzlement, employee theft and other crimes. [quoted text, click to view] >> This query is very important to us and because of orders table size, it has become very slow. <<Are you running over a terabyte? This query is more important than basic accounting checks and balances are? And I would bet that queries are slow because of poor schema design, not table size. Usually, a schema with this lack of controls tends to accumulate garbage and orphan rows in the tables until it chokes. You probably have more troubles than can be handled on a Newsgroup. --CELKO-- Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. Sample data is also a good idea, along with clear specifications. *** Sent via Developersdex http://www.developersdex.com ***
Can you describe what is slow? Like returning all rows, or with certain
where clauses? And how do you use the view? How many places do you use this view? A lot will depend on how many different places you use the view, possibly. -- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:u0OOUKA0EHA.4004@tk2msftngp13.phx.gbl... > Hi, > I have a database with structure similar to Northwind. When the users > place > orders, the customerid and employeeid are optional. Therefore when I want > to > create reports of sales, I use outer join like this: > > select c.companyname, o.orderdate, o.shipcountry, e.lastname from orders o > left join customers c on o.customerid=c.customerid > left join employees e on o.employeeid=e.employeeid > > This query is very important to us and because of orders table size, it > has > become very slow. I am thinking of using an indexed view but outer join > are > not allowed. I'm going to insert a row in customers table for unregistered > customers and a row into employees table for orders inserted without > employeeid so that I can use inner join and create index for this > query(view). > Yet I have not changed anything and I decided to post my question first to > know ideas whether this is a good solution or still there're other tricks > to > improve this view's performance(Tables have appropriate indexes and I > believe no optimization can be done because the execution plan is good) > Thanks in advance. > Leila > >
Hi,
I have a database with structure similar to Northwind. When the users place orders, the customerid and employeeid are optional. Therefore when I want to create reports of sales, I use outer join like this: select c.companyname, o.orderdate, o.shipcountry, e.lastname from orders o left join customers c on o.customerid=c.customerid left join employees e on o.employeeid=e.employeeid This query is very important to us and because of orders table size, it has become very slow. I am thinking of using an indexed view but outer join are not allowed. I'm going to insert a row in customers table for unregistered customers and a row into employees table for orders inserted without employeeid so that I can use inner join and create index for this query(view). Yet I have not changed anything and I decided to post my question first to know ideas whether this is a good solution or still there're other tricks to improve this view's performance(Tables have appropriate indexes and I believe no optimization can be done because the execution plan is good) Thanks in advance. Leila
[quoted text, click to view]
On Sun, 21 Nov 2004 23:44:14 +0330, Leila wrote: >Louis, >most of the times, the view is used without any where clause to return all >records. it is used a lot and the speed is critical. Hi Leila, If everything you write in your previous message is true (i.e. you have appropriate indexes and execution plan is good), then this query must be returning massive amounts of data. I think you have millions of orders in your table. Correct? This is why your above statement surprises me. Do you really mean that people at your company have to manually inspect millions of rows of data? And that they can review this data so fast that speed is important? I suggest you look into the places where this query is used first. You'll probably find that most users really do not appreciate all that data being pushed to their screens. If you can set up some filters, they will be delighted because they don't get distracted by "noise" anymore - and performance will rise too. If you really must return all rows everytime, you should first start investing in your network - network speed is most often the bottleneck on queries that return gadzillions of bytes. After that, see if the query starts returning the first rows sooner if you add an OPTION (FAST n) optimizer hint. The total execution time might increase, but it will never exceed the time a human requires to peruse millions of lines of data. Best, Hugo --
Louis,
most of the times, the view is used without any where clause to return all records. it is used a lot and the speed is critical. I think I must create nonclustered indexes on indexed view when I use where clause? [quoted text, click to view] "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message news:elZZMVA0EHA.4004@tk2msftngp13.phx.gbl... > Can you describe what is slow? Like returning all rows, or with certain > where clauses? And how do you use the view? How many places do you use > this view? A lot will depend on how many different places you use the view, > possibly. > > -- > -------------------------------------------------------------------------- -- > Louis Davidson - drsql@hotmail.com > SQL Server MVP > > Compass Technology Management - www.compass.net > Pro SQL Server 2000 Database Design - > http://www.apress.com/book/bookDisplay.html?bID=266 > Note: Please reply to the newsgroups only unless you are interested in > consulting services. All other replies may be ignored :) > > "Leila" <Leilas@hotpop.com> wrote in message > news:u0OOUKA0EHA.4004@tk2msftngp13.phx.gbl... > > Hi, > > I have a database with structure similar to Northwind. When the users > > place > > orders, the customerid and employeeid are optional. Therefore when I want > > to > > create reports of sales, I use outer join like this: > > > > select c.companyname, o.orderdate, o.shipcountry, e.lastname from orders o > > left join customers c on o.customerid=c.customerid > > left join employees e on o.employeeid=e.employeeid > > > > This query is very important to us and because of orders table size, it > > has > > become very slow. I am thinking of using an indexed view but outer join > > are > > not allowed. I'm going to insert a row in customers table for unregistered > > customers and a row into employees table for orders inserted without > > employeeid so that I can use inner join and create index for this > > query(view). > > Yet I have not changed anything and I decided to post my question first to > > know ideas whether this is a good solution or still there're other tricks > > to > > improve this view's performance(Tables have appropriate indexes and I > > believe no optimization can be done because the execution plan is good) > > Thanks in advance. > > Leila > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |