dinesh_bali
asked on
Fetching data is taking time from database
Hi,
I have around 400,000 records in the database. I am using MS SQL 2005
I have created indexes for the required fields in all the tables to which I am joining.
SQL take lot of time to fetch the data sometime around 1.20 min
Can anyone help me that what should I do to retieve data fast.
Kind Regards,
Dinesh
I have around 400,000 records in the database. I am using MS SQL 2005
I have created indexes for the required fields in all the tables to which I am joining.
SQL take lot of time to fetch the data sometime around 1.20 min
Can anyone help me that what should I do to retieve data fast.
Kind Regards,
Dinesh
Hi,
The time taken would depend on the following
1. The hardware you are using (processor, RAM, hard disc, etc)
2. The parameters set for SQL Server
3. The manner in which the query is written
4. The size of the data being queried
5. The size of the output data being fetched
4. The manner in which the output is retrieved
5. Other factors ;-)
I undersand that you have only 400,000 rows in the entire database and you also have the necessary indexes. That should rule out the first 2 points. But how many rows do you have in each of the tables being queried? and what is the size of the output? And where are you executing the query from? Also, can you check the execution plan in the query analyser and see the operation that is taking the maximum resources (seen as % of the total resources taken) and if the query is using the necessary indexes? Also, in some cases, adding hints can help substansially. I can help you out if you can paste the query along with the execution plan in XML.
The time taken would depend on the following
1. The hardware you are using (processor, RAM, hard disc, etc)
2. The parameters set for SQL Server
3. The manner in which the query is written
4. The size of the data being queried
5. The size of the output data being fetched
4. The manner in which the output is retrieved
5. Other factors ;-)
I undersand that you have only 400,000 rows in the entire database and you also have the necessary indexes. That should rule out the first 2 points. But how many rows do you have in each of the tables being queried? and what is the size of the output? And where are you executing the query from? Also, can you check the execution plan in the query analyser and see the operation that is taking the maximum resources (seen as % of the total resources taken) and if the query is using the necessary indexes? Also, in some cases, adding hints can help substansially. I can help you out if you can paste the query along with the execution plan in XML.
Hi dinesh_bali,
post the query , table structures and exsisting indexes so we can optimise your query/indexes
HTH
R.
post the query , table structures and exsisting indexes so we can optimise your query/indexes
HTH
R.
ASKER
Hi,
Thanks for your reply.
Below is my computer configuration
I am using Pentium 4
CPU 3.00 GHz
504 MB of RAM
Also please tell me how to check
1. The parameters set for SQL Server? Also what it should be.
2. Execution plan in XML
Currently I am trying to fetch only one record for specific email
SELECT CUS.CTCT_ID, CUS.CTCT_CREATIONDATE, CUS.CTCT_LASTACCESSDATE,
CUS.CTCT_TITLE, CUS.CTCT_FIRSTNAME, CUS.CTCT_LASTNAME, CUS.CTCT_PASSWORD,
CUS.CTCT_ADDRESSLINE1, CUS.CTCT_ADDRESSLINE2, CUS.CTCT_ADDRESSLINE3,
CUS.CTCT_BUILDINGNUMBER, CUS.CTCT_CITY, CUS.CTCT_POSTCODE, CUS.CTCT_STATE_COUNTY,
CUS.COCU_ID, CUS.CTCT_EMAIL, CUS.CTCT_NATIONALITY, CUS.CTPL_ID, DOP.DCOP_CULTURE ,DOP.DCOP_ID,
DOP.DCOP_MARKETINGCONTACTE MAIL, DOP.DCOP_MARKETINGCONTACTE POSTAL,
DOP.DCOP_MARKETINGCONTACTE THIRDPARTY ,CNY.COCU_ CODE, PFL.CTPL_CODE
FROM CT_CUSTOMER CUS
INNER JOIN DC_DISTRIBUTIONCHANNELOPTI ONS DOP ON DOP.CTCT_ID = CUS.CTCT_ID
INNER JOIN DC_DISTRIBUTIONCHANNEL DCH ON DCH.DCDC_ID = DOP.DCDC_ID
INNER JOIN DC_DISTRIBUTIONCHANNELTYPE DTY ON DTY.DCDT_TYPE = DCH.DCDT_TYPE
INNER JOIN CO_COUNTRY CNY ON CNY.COCU_ID = CUS.COCU_ID
LEFT OUTER JOIN CT_PREFERREDLANGUAGE PFL ON CUS.CTPL_ID = PFL.CTPL_ID
WHERE
DTY.DCDT_DESCRIPTION = 'abc'
AND CUS.CTCT_EMAIL = 'my email'
Thanks again
Kind Regards,
Dinesh
Thanks for your reply.
Below is my computer configuration
I am using Pentium 4
CPU 3.00 GHz
504 MB of RAM
Also please tell me how to check
1. The parameters set for SQL Server? Also what it should be.
2. Execution plan in XML
Currently I am trying to fetch only one record for specific email
SELECT CUS.CTCT_ID, CUS.CTCT_CREATIONDATE, CUS.CTCT_LASTACCESSDATE,
CUS.CTCT_TITLE, CUS.CTCT_FIRSTNAME, CUS.CTCT_LASTNAME, CUS.CTCT_PASSWORD,
CUS.CTCT_ADDRESSLINE1, CUS.CTCT_ADDRESSLINE2, CUS.CTCT_ADDRESSLINE3,
CUS.CTCT_BUILDINGNUMBER, CUS.CTCT_CITY, CUS.CTCT_POSTCODE, CUS.CTCT_STATE_COUNTY,
CUS.COCU_ID, CUS.CTCT_EMAIL, CUS.CTCT_NATIONALITY, CUS.CTPL_ID, DOP.DCOP_CULTURE ,DOP.DCOP_ID,
DOP.DCOP_MARKETINGCONTACTE
DOP.DCOP_MARKETINGCONTACTE
FROM CT_CUSTOMER CUS
INNER JOIN DC_DISTRIBUTIONCHANNELOPTI
INNER JOIN DC_DISTRIBUTIONCHANNEL DCH ON DCH.DCDC_ID = DOP.DCDC_ID
INNER JOIN DC_DISTRIBUTIONCHANNELTYPE
INNER JOIN CO_COUNTRY CNY ON CNY.COCU_ID = CUS.COCU_ID
LEFT OUTER JOIN CT_PREFERREDLANGUAGE PFL ON CUS.CTPL_ID = PFL.CTPL_ID
WHERE
DTY.DCDT_DESCRIPTION = 'abc'
AND CUS.CTCT_EMAIL = 'my email'
Thanks again
Kind Regards,
Dinesh
how many records does thisquery return?
dinesh_bali,
First make sure all the below tables have clusterd indexes on them
DC_DISTRIBUTIONCHANNELOPTI ONS
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE
If the below fields are not indexed create indexes on them
DC_DISTRIBUTIONCHANNELOPTI ONS.CTCT_I D
DC_DISTRIBUTIONCHANNELOPTI ONS.DCDC_I D
CT_CUSTOMER.CTCT_ID
CT_CUSTOMER.CTPL_ID
CT_CUSTOMER.COCU_ID
DC_DISTRIBUTIONCHANNEL.DCD C_ID
DC_DISTRIBUTIONCHANNEL.DCD T_TYPE
DC_DISTRIBUTIONCHANNELTYPE .DCDT_TYPE
CO_COUNTRY.COCU_ID
CT_PREFERREDLANGUAGE.CTPL_ ID
First make sure all the below tables have clusterd indexes on them
DC_DISTRIBUTIONCHANNELOPTI
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE
If the below fields are not indexed create indexes on them
DC_DISTRIBUTIONCHANNELOPTI
DC_DISTRIBUTIONCHANNELOPTI
CT_CUSTOMER.CTCT_ID
CT_CUSTOMER.CTPL_ID
CT_CUSTOMER.COCU_ID
DC_DISTRIBUTIONCHANNEL.DCD
DC_DISTRIBUTIONCHANNEL.DCD
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY.COCU_ID
CT_PREFERREDLANGUAGE.CTPL_
ASKER
Hi,
Thanks for your prompt reply and the way you are helping
My this query returns only one record
I have created the indexes in the following way:
CREATE INDEX CTCT_ID_IND
ON CT_CUSTOMER (CTCT_ID)
GO
CREATE INDEX DOP_CTCT_ID_IND
ON DC_DISTRIBUTIONCHANNELOPTI ONS (CTCT_ID)
GO
CREATE INDEX DCDC_ID_ID_IND
ON DC_DISTRIBUTIONCHANNELOPTI ONS (DCDC_ID)
GO
CREATE INDEX DCOP_ID_IND
ON DC_DISTRIBUTIONCHANNELOPTI ONS (DCOP_ID)
GO
Can you please help me how to creating clustured indexes and also tell me how to check if this exist or not
Kind regards,
Dinesh
Thanks for your prompt reply and the way you are helping
My this query returns only one record
I have created the indexes in the following way:
CREATE INDEX CTCT_ID_IND
ON CT_CUSTOMER (CTCT_ID)
GO
CREATE INDEX DOP_CTCT_ID_IND
ON DC_DISTRIBUTIONCHANNELOPTI
GO
CREATE INDEX DCDC_ID_ID_IND
ON DC_DISTRIBUTIONCHANNELOPTI
GO
CREATE INDEX DCOP_ID_IND
ON DC_DISTRIBUTIONCHANNELOPTI
GO
Can you please help me how to creating clustured indexes and also tell me how to check if this exist or not
Kind regards,
Dinesh
>>Can you please help me how to creating clustured indexes and also tell me how to check if this exist or not
to create clustered index you have to add clustered keyword which creating the the index
for example
CREATE clustered INDEX DCOP_ID_IND
ON DC_DISTRIBUTIONCHANNELOPTI ONS (DCOP_ID)
GO
to check if index exists or not
you have to check
sysindexes table. or simply in query analyzer highlight yourtable and prss <alt>+<f1>
to create clustered index you have to add clustered keyword which creating the the index
for example
CREATE clustered INDEX DCOP_ID_IND
ON DC_DISTRIBUTIONCHANNELOPTI
GO
to check if index exists or not
you have to check
sysindexes table. or simply in query analyzer highlight yourtable and prss <alt>+<f1>
dinesh_bali,
run the following for each table
sp_helpindex TableName
this will tell you all the indexes on a table and their type
run the following for each table
sp_helpindex TableName
this will tell you all the indexes on a table and their type
ASKER
Hi, I have checked all the indexes and everything seems fine.
Still the query has given me the result in 55 sec, Query has fetched only one record in 55 sec.
Also, For the above mentioned tables on the primary key
There was already clustered indexes made on primary key before and I have made another non- clustered index for their primary key.
Will it make any difference? I mean clustered and non -clustered index for same field?
Pls. give me some more suggestions to help me to make my result faster?
Kind Regards,
Dinesh
Still the query has given me the result in 55 sec, Query has fetched only one record in 55 sec.
Also, For the above mentioned tables on the primary key
There was already clustered indexes made on primary key before and I have made another non- clustered index for their primary key.
Will it make any difference? I mean clustered and non -clustered index for same field?
Pls. give me some more suggestions to help me to make my result faster?
Kind Regards,
Dinesh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dinesh_bali,
what one of these tables has the most data?
DC_DISTRIBUTIONCHANNELOPTI ONS
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE
what one of these tables has the most data?
DC_DISTRIBUTIONCHANNELOPTI
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE
ASKER
Thanks for all you are trying to help me
Here is the details you asked:
select count(*) from CT_CUSTOMER // Total Count 363469
select count(*) from DC_DISTRIBUTIONCHANNELOPTI ONS // Total Count 364498
select count(*) from DC_DISTRIBUTIONCHANNEL // Total Count 10
select count(*) from DC_DISTRIBUTIONCHANNELTYPE // Total Count 2
select count(*) from CO_COUNTRY // Total Count 40
select count(*) from CT_PREFERREDLANGUAGE // Total Count 24
First time when I run a query it takes around 1 min to get the result. but if I run it again and again then the results comes fast.
I want the result to come fast from the first attempt.
Kind Regards,
Dinesh
Here is the details you asked:
select count(*) from CT_CUSTOMER // Total Count 363469
select count(*) from DC_DISTRIBUTIONCHANNELOPTI
select count(*) from DC_DISTRIBUTIONCHANNEL // Total Count 10
select count(*) from DC_DISTRIBUTIONCHANNELTYPE
select count(*) from CO_COUNTRY // Total Count 40
select count(*) from CT_PREFERREDLANGUAGE // Total Count 24
First time when I run a query it takes around 1 min to get the result. but if I run it again and again then the results comes fast.
I want the result to come fast from the first attempt.
Kind Regards,
Dinesh
ASKER
I tried to run Tunning Advisor?
I get the following index recommendation:
use [ENET_LIVE_NEW]
go
CREATE NONCLUSTERED INDEX [_dta_index_CT_CUSTOMER_8_ 1386540073 __K25_K1_K 15_K29_2_3 _4_5_6_7_8 _9_10_11_1 2_13_14_27 ] ON [dbo].[CT_CUSTOMER]
(
[CTCT_EMAIL] ASC,
[CTCT_ID] ASC,
[COCU_ID] ASC,
[CTPL_ID] ASC
)
INCLUDE ( [CTCT_CREATIONDATE],
[CTCT_LASTACCESSDATE],
[CTCT_TITLE],
[CTCT_FIRSTNAME],
[CTCT_LASTNAME],
[CTCT_PASSWORD],
[CTCT_ADDRESSLINE1],
[CTCT_ADDRESSLINE2],
[CTCT_ADDRESSLINE3],
[CTCT_BUILDINGNUMBER],
[CTCT_CITY],
[CTCT_POSTCODE],
[CTCT_STATE_COUNTY],
[CTCT_NATIONALITY]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_1386540073_15_1 _29] ON [dbo].[CT_CUSTOMER]([COCU_ ID], [CTCT_ID], [CTPL_ID])
go
CREATE STATISTICS [_dta_stat_1386540073_25_2 9_15] ON [dbo].[CT_CUSTOMER]([CTCT_ EMAIL], [CTPL_ID], [COCU_ID])
go
CREATE STATISTICS [_dta_stat_1386540073_25_1 5_1_29] ON [dbo].[CT_CUSTOMER]([CTCT_ EMAIL], [COCU_ID], [CTCT_ID], [CTPL_ID])
go
CREATE NONCLUSTERED INDEX [_dta_index_DC_DISTRIBUTIO NCHANNELOP TIONS_8_15 95920807__ K3_K1_K2_4 _5_6_7] ON [dbo].[DC_DISTRIBUTIONCHAN NELOPTIONS ]
(
[CTCT_ID] ASC,
[DCOP_ID] ASC,
[DCDC_ID] ASC
)
INCLUDE ( [DCOP_CULTURE],
[DCOP_MARKETINGCONTACTEMAI L],
[DCOP_MARKETINGCONTACTEPOS TAL],
[DCOP_MARKETINGCONTACTETHI RDPARTY]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_1595920807_1_3] ON [dbo].[DC_DISTRIBUTIONCHAN NELOPTIONS ]([DCOP_ID ], [CTCT_ID])
go
CREATE STATISTICS [_dta_stat_1595920807_2_1_ 3] ON [dbo].[DC_DISTRIBUTIONCHAN NELOPTIONS ]([DCDC_ID ], [DCOP_ID], [CTCT_ID])
go
NOW WHAT I SHOULD DO?
I get the following index recommendation:
use [ENET_LIVE_NEW]
go
CREATE NONCLUSTERED INDEX [_dta_index_CT_CUSTOMER_8_
(
[CTCT_EMAIL] ASC,
[CTCT_ID] ASC,
[COCU_ID] ASC,
[CTPL_ID] ASC
)
INCLUDE ( [CTCT_CREATIONDATE],
[CTCT_LASTACCESSDATE],
[CTCT_TITLE],
[CTCT_FIRSTNAME],
[CTCT_LASTNAME],
[CTCT_PASSWORD],
[CTCT_ADDRESSLINE1],
[CTCT_ADDRESSLINE2],
[CTCT_ADDRESSLINE3],
[CTCT_BUILDINGNUMBER],
[CTCT_CITY],
[CTCT_POSTCODE],
[CTCT_STATE_COUNTY],
[CTCT_NATIONALITY]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_1386540073_15_1
go
CREATE STATISTICS [_dta_stat_1386540073_25_2
go
CREATE STATISTICS [_dta_stat_1386540073_25_1
go
CREATE NONCLUSTERED INDEX [_dta_index_DC_DISTRIBUTIO
(
[CTCT_ID] ASC,
[DCOP_ID] ASC,
[DCDC_ID] ASC
)
INCLUDE ( [DCOP_CULTURE],
[DCOP_MARKETINGCONTACTEMAI
[DCOP_MARKETINGCONTACTEPOS
[DCOP_MARKETINGCONTACTETHI
go
CREATE STATISTICS [_dta_stat_1595920807_1_3]
go
CREATE STATISTICS [_dta_stat_1595920807_2_1_
go
NOW WHAT I SHOULD DO?
Add *ONE* index, as follows:
CREATE INDEX CTCT_EMAIL_IND
ON CT_CUSTOMER (CTCT_EMAIL)
CREATE INDEX CTCT_EMAIL_IND
ON CT_CUSTOMER (CTCT_EMAIL)
ASKER
My problem is solved because of running sql tuning advisor and checking my query for index recommendation as guided by imran_fast
Thanks all of you, who has tried to help me.
Kind Regards,
Dinesh
Can you post your query?