We help IT Professionals succeed at work.

Fetching data is taking time from database

dinesh_bali
dinesh_bali asked
on
Medium Priority
230 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

Hi,

Can you post your query?

Commented:
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.



CERTIFIED EXPERT

Commented:
Hi dinesh_bali,

post the query , table structures and exsisting indexes so we can optimise your query/indexes

HTH

R.

Author

Commented:
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_MARKETINGCONTACTEMAIL, DOP.DCOP_MARKETINGCONTACTEPOSTAL,
            DOP.DCOP_MARKETINGCONTACTETHIRDPARTY,CNY.COCU_CODE, PFL.CTPL_CODE
FROM CT_CUSTOMER CUS
            INNER JOIN DC_DISTRIBUTIONCHANNELOPTIONS 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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
how many records does thisquery return?
CERTIFIED EXPERT

Commented:
dinesh_bali,

First make sure all the below tables have clusterd indexes on them

DC_DISTRIBUTIONCHANNELOPTIONS
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE

If the below fields are not indexed create indexes on them

DC_DISTRIBUTIONCHANNELOPTIONS.CTCT_ID
DC_DISTRIBUTIONCHANNELOPTIONS.DCDC_ID

CT_CUSTOMER.CTCT_ID
CT_CUSTOMER.CTPL_ID
CT_CUSTOMER.COCU_ID

DC_DISTRIBUTIONCHANNEL.DCDC_ID
DC_DISTRIBUTIONCHANNEL.DCDT_TYPE

DC_DISTRIBUTIONCHANNELTYPE.DCDT_TYPE

CO_COUNTRY.COCU_ID

CT_PREFERREDLANGUAGE.CTPL_ID

Author

Commented:
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_DISTRIBUTIONCHANNELOPTIONS (CTCT_ID)

GO

CREATE INDEX DCDC_ID_ID_IND

    ON DC_DISTRIBUTIONCHANNELOPTIONS (DCDC_ID)
   
    GO

CREATE INDEX DCOP_ID_IND

    ON DC_DISTRIBUTIONCHANNELOPTIONS (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
Top Expert 2006

Commented:
>>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_DISTRIBUTIONCHANNELOPTIONS (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>
CERTIFIED EXPERT

Commented:
dinesh_bali,

run the following for each table

sp_helpindex TableName

this will tell you all the indexes on a table and their type

Author

Commented:
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
Top Expert 2006
Commented:
run sql tuning advisor and check your query for index recommendation.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
dinesh_bali,

what one of these tables has the most data?

DC_DISTRIBUTIONCHANNELOPTIONS
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE

Author

Commented:
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_DISTRIBUTIONCHANNELOPTIONS // 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

Author

Commented:
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_K15_K29_2_3_4_5_6_7_8_9_10_11_12_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_29_15] ON [dbo].[CT_CUSTOMER]([CTCT_EMAIL], [CTPL_ID], [COCU_ID])
go

CREATE STATISTICS [_dta_stat_1386540073_25_15_1_29] ON [dbo].[CT_CUSTOMER]([CTCT_EMAIL], [COCU_ID], [CTCT_ID], [CTPL_ID])
go

CREATE NONCLUSTERED INDEX [_dta_index_DC_DISTRIBUTIONCHANNELOPTIONS_8_1595920807__K3_K1_K2_4_5_6_7] ON [dbo].[DC_DISTRIBUTIONCHANNELOPTIONS]
(
      [CTCT_ID] ASC,
      [DCOP_ID] ASC,
      [DCDC_ID] ASC
)
INCLUDE ( [DCOP_CULTURE],
[DCOP_MARKETINGCONTACTEMAIL],
[DCOP_MARKETINGCONTACTEPOSTAL],
[DCOP_MARKETINGCONTACTETHIRDPARTY]) 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_DISTRIBUTIONCHANNELOPTIONS]([DCOP_ID], [CTCT_ID])
go

CREATE STATISTICS [_dta_stat_1595920807_2_1_3] ON [dbo].[DC_DISTRIBUTIONCHANNELOPTIONS]([DCDC_ID], [DCOP_ID], [CTCT_ID])
go



NOW WHAT I SHOULD DO?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Add *ONE* index, as follows:

CREATE INDEX CTCT_EMAIL_IND
    ON CT_CUSTOMER (CTCT_EMAIL)

Author

Commented:

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


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.