March 18, 2008 at 1:57 pm
praseodymiumm (3/18/2008)
Thank GSquared! I really appreciate your help.If I could ask another question?
These queries will be used as data sources to build OLAP cubes (Cognos Transformer). Depending on which cube I'm building, I may be requesting a different set of fields from each of these tables. Should I be creating a specific index for each of these queries?
For example, anytime idh_hist is queried, idh_inv_nbr, idh_nbr, idh_line, idh_part, idh_qty_inv are always requested. But, in certain cases, I may also request other fields. Should I create one index that includes those five fields, and then another that includes those five, plus the other fields I require?
Thanks again.
Usually, building OLAP cubes doesn't require fast queries. Or does Cognos not persist the cube as a separate object? If you're loading data into OLAP cubes, the speed of using indexes may not be worth the cost of building and maintaining them.
Think about that one carefully. If you're loading data over night, and it takes 30 minutes to complete without indexes, but takes 2 minutes to complete with indexes, but nobody is going to look at the data till six hours later, does the load speed really matter?
If it does, go ahead with indexing for it. I don't know your needs on this, I'm just going by general principles on the thing.
As far as the columns indexed, I'd recommend using the Where and Join columns in the main part of the index, then put your other Select columns in the Include part of the index. That way, if you have multiple queries that use similar joins and similar where conditions, but which return different columns in the select clause, they can all use the same index, which will save you significant disk space.
The main thing to keep in mind is: A covering index makes a select fast, but makes update/insert/delete slower, and uses up a large amount of disk space. Multiple covering indexes on the same table is usually (not always) just asking for trouble.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2008 at 4:16 pm
Lynn Pettis (3/17/2008)
I have looked at the two files with CREATE Table statements, and I'm not going to do anything with those as it is too hard to see what's what. Also, there are 5 tables involved and I could only see 2 in the files.Suggestion, provide well formatted DDL for all 5 tables (including indexes) that includes the fields involved in the query plus those in indexes. We don't need any extranous info if it isn't involved in the query.
😎
Perhaps a link to some WIKI with the definition of "Well Formatted" would have worked... wonder if the OP likes porkchops. Moving on to an easier to read bit of code...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2008 at 4:25 pm
You think?? :hehe:
March 19, 2008 at 6:52 am
GSquared (3/18/2008)
Usually, building OLAP cubes doesn't require fast queries. Or does Cognos not persist the cube as a separate object? If you're loading data into OLAP cubes, the speed of using indexes may not be worth the cost of building and maintaining them.
Think about that one carefully. If you're loading data over night, and it takes 30 minutes to complete without indexes, but takes 2 minutes to complete with indexes, but nobody is going to look at the data till six hours later, does the load speed really matter?
If it does, go ahead with indexing for it. I don't know your needs on this, I'm just going by general principles on the thing.
You're right, it may not be worth the cost of building and maintaining them.
Thanks for the advice and all your help over the past couple days.
March 19, 2008 at 6:54 am
Jeff Moden (3/18/2008)
Lynn Pettis (3/17/2008)
I have looked at the two files with CREATE Table statements, and I'm not going to do anything with those as it is too hard to see what's what. Also, there are 5 tables involved and I could only see 2 in the files.Suggestion, provide well formatted DDL for all 5 tables (including indexes) that includes the fields involved in the query plus those in indexes. We don't need any extranous info if it isn't involved in the query.
😎
Perhaps a link to some WIKI with the definition of "Well Formatted" would have worked... wonder if the OP likes porkchops. Moving on to an easier to read bit of code...
For future reference, would you able to provide me with a link? I apologize for not following the rules.
March 19, 2008 at 7:03 am
No link required, actually... The term "Well formatted" should be common knowledge. But even if it's not, here's what your text attachments look like because you have no carriage returns and no indention...
CREATE TABLE [dbo].[ih_hist] ( [ih_nbr] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ih_cust] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ih_inv_nbr] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ih_inv_date] [smalldatetime] NULL , ) ON [PRIMARY] GO CREATE INDEX [ih_hist##ih_cust] ON [dbo].[ih_hist]([ih_cust], [ih_inv_date]) ON [PRIMARY] GO CREATE INDEX [ih_hist##ih_inv_date] ON [dbo].[ih_hist]([ih_inv_date], [ih_inv_nbr]) ON [PRIMARY] GO CREATE UNIQUE INDEX [ih_hist##ih_inv_nbr] ON [dbo].[ih_hist]([ih_inv_nbr], [ih_nbr]) ON [PRIMARY] GO CREATE INDEX [ih_hist##ih_nbr] ON [dbo].[ih_hist]([ih_nbr], [ih_inv_date]) ON [PRIMARY] GO
While I appreciate the effort you took to get that data, it would be a lot better if you used a file save from the SSMS editor window instead of whatever you're doing to create this file. Some of us won't even look at code like this simply because it's a hard read and we've got better things to do that struggle with your code.
Anyway... thanks for your response... and sorry... I was having a bit of fun at your expense because of your code like above...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2008 at 8:22 am
Taking the code posted be Jeff, here is one example of well formatted code.
CREATE TABLE [dbo].[ih_hist] (
[ih_nbr] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ih_cust] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ih_inv_nbr] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ih_inv_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE INDEX [ih_hist##ih_cust] ON [dbo].[ih_hist](
[ih_cust],
[ih_inv_date]
) ON [PRIMARY]
GO
CREATE INDEX [ih_hist##ih_inv_date] ON [dbo].[ih_hist](
[ih_inv_date],
[ih_inv_nbr]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [ih_hist##ih_inv_nbr] ON [dbo].[ih_hist](
[ih_inv_nbr],
[ih_nbr]
) ON [PRIMARY]
GO
CREATE INDEX [ih_hist##ih_nbr] ON [dbo].[ih_hist](
[ih_nbr], [ih_inv_date]
) ON [PRIMARY]
GO
I am sure other will chime in and possibly present other examples, but if you compare what I posted above to that in Jeff's post, I am sure you will see why we ask for well formatted code. It is just easier read and figure out what is going on.
😎
March 19, 2008 at 12:23 pm
Man, I feel like such an idiot. I didn't realize that's what my code looked like. When I saved it, it was "well defined".
Lesson learned. Thanks!
March 19, 2008 at 12:24 pm
praseodymiumm (3/19/2008)
Man, I feel like such an idiot. I didn't realize that's what my code looked like. When I saved it, it looked "well defined".Lesson learned. Thanks!
By the way - for better or worse - using the CODE tags on the left sometimes help keep some of the formatting. Of course - sometimes it doesn't too....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply