September 28, 2009 at 11:15 pm
Hi,
Here is my tables structure.
My tbl_StockMaster will have around 20 records.
My tbl_StockData will have crores of records as it's a hourly data nad is going on increasing.
I want to know how can i apply indexing to my tables so that data is fectched efficiently and fast.
1. tbl_StockMaster
iStockId int
vcStockCode varchar(100)
2. tbl_StockData
iDataId int
dtDate datetime
iStockId int
decQuote decimal(14,2)
decSell decimal(14,2)
decBuy decimal(14,2)
In UI i have an option where user can select his stocks and view the selected data,
so i do a join in my query to tbl_StockMaster and fetch only those stock's records which user have selected.
[Code]
select ....
(
select .... where dtDate<...
)P
inner join @tbl_SelectedStocks as C on C.id=P.iStockId
inner join tbl_StockMaster M on P.iStockId=M.iStockId
[/Code]
The different Where clause used in my select queries are,
[Code]
where datediff(dd,dtDate,getdate())<@n+1
and convert(datetime,convert(varchar,dtDate,101))<=convert(datetime,convert(varchar,getdate(),101))
and dtDate>'2004-06-23 00:00:00:00'
group by convert(varchar,dtDate,101),vcStockCode
where dtDate>=@StartDate and dtDate<dateadd(dd,1,@EndDate)
and dtDate>'2004-06-23 00:00:00:00'
group by convert(varchar,dtDate,101),vcStockCode
[/Code]
At places i am also doing order by,
[Code]
order by datepart(hh,dtDate)
[/Code]
At places i do group by,
[Code]
group by datepart(yy,dtDate), datepart(ww,dtDate)
[/Code]
Currently i have not applied any indexes.
Also please guide how can i quickly select from a table with crores of records with joins and where clause.
Thanks.
September 29, 2009 at 9:57 am
The first recommendation I'd make is that you need to remove the functions that are being used against columns in your WHERE clause. Basically using function against a column in a WHERE clause means that the best you can get is an index scan against any index because the function has to be applied against every row before SQL Server can do any comparison.
Do you have primary keys on your tables? By default your primary key is also a clustered index. So if you do have PK's what are they?
Based on the information provided I'd start with an index on tbl_StockData on iStockId, dtDate include any other columns in the select so that the index is covering as those are in your join and where clause. Because you say that tbl_StockMaster will only have 20 rows you probably don't need any indexes on that table as it will only be 1 Page so the optimizer will most likely choose a table/Clustered Index scan over an index anyway.
If you really need to group by the year and the week, I would suggest an calendar table that contains those columns as that will likely be faster than the functions. Check out this blog post[/url] for why and how to build one.
If you want to order by the hour you should just order by dtDate instead of the function. That will likely give you the same ordering.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 29, 2009 at 10:12 pm
Hi, Thanks Jack ofr your valuable suggestions. I would like to add few more details to my structure.
1. In tbl_StockMaster ,iStockId is primary key.
2. While in tbl_StockData there is no primary key and now unique key and no constriants.
3. In tbl_StockData dtDate is in datetime and it has valuable date and hour part.
4. In select statements i need to use the datetime functions to fetch the hour part as I dont have a seperate column for hour part.
5. I can say that in tbl_StockData dtDate and iStockId will be unique.
i.e. for a particular hour of a day there cannot be two entries for same hour and stockid.
September 30, 2009 at 1:11 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply