| Author |
Topic |
|
abcd
Yak Posting Veteran
92 Posts |
 Posted - 2008-11-25 : 23:31:26
|
handling large databases in sql having around lakh number of rows... |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-26 : 00:20:10
|
| JUST 0.1 million ???? When u talk about LARGE database you must say data in TERABYTES |
 |
|
|
abcd
Yak Posting Veteran
92 Posts |
Posted - 2008-11-26 : 00:57:57
|
| making my problem much specific here is the detail....there is a database ,having approx 50 lakhs records.main problem is coming in execution in select query .. we have already indexed the table ...but server is taking around 30 seconds to exectue. what are the possible ways to make the query execute faster.it is also taking large time using aggregate function |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-11-26 : 01:29:11
|
If you can post-create table-sample data-your select query-needed outputmaybe someone can help!Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
abcd
Yak Posting Veteran
92 Posts |
Posted - 2008-11-26 : 02:22:21
|
| a small fragment of the tables present is as follows: there is a table product_tablehaving following fields :-product_id (primary key)product_description nvarchar(max) assess_values nvarchar(max) unit_usd nvarchar(max) unit_inr nvarchar(max) unitprice nvarchar(max) query is select product_description from product_table where product_description like %'cable'% product_id is the field on which indexing has been applied |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-26 : 02:38:56
|
| Columns in WHERE clause should have indexes, but keep in mind that "Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index" |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-11-26 : 03:25:39
|
Maybe you can use Fulltext Index to speed up your select.http://msdn.microsoft.com/en-us/library/ms187317(SQL.90).aspxBtw, don't use always varchar(max) when it is not obvious.unitprice or unit_inr seems to be better a numeric datatype...Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 07:00:17
|
This doesn't use index and goes for scan.quote:
Originally posted by abcd
a small fragment of the tables present is as follows: there is a table product_tablehaving following fields :-product_id (primary key)product_description nvarchar(max) assess_values nvarchar(max) unit_usd nvarchar(max) unit_inr nvarchar(max) unitprice nvarchar(max) query is select product_description from product_table where product_description like %'cable'% product_id is the field on which indexing has been applied
|
|
|
|
abcd
Yak Posting Veteran
92 Posts |
Posted - 2008-11-26 : 07:20:44
|
| so can u suggest how to make the use of indexes ??/we have a clusterd index on production_idand non clusterd index on product_description.how to make use of indexes?? |
|
|
|
ECS_Dale
Starting Member
5 Posts |
Posted - 2008-11-26 : 08:05:16
|
| It's possibly a bit late now but why is there no product category field? You couls have an index on that and another clause in the WHERE statement to retrieve fewer rows for the query to scan.Is there any form of logic in the product ID you could use to achieve the same thing or has it been set up as an identity or something similar? |
|
|
|
|
handlin large database