Solved

Fetching data is taking time from database

Posted on 2006-11-08
16
204 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
0
Comment
Question by:dinesh_bali
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +4
16 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 17896847
Hi,

Can you post your query?
0
 
LVL 1

Expert Comment

by:Yogeshup
ID: 17896985
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.



0
 
LVL 11

Expert Comment

by:regbes
ID: 17897011
Hi dinesh_bali,

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

HTH

R.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:dinesh_bali
ID: 17897034
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17897039
how many records does thisquery return?
0
 
LVL 11

Expert Comment

by:regbes
ID: 17897064
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
0
 

Author Comment

by:dinesh_bali
ID: 17897153
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
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17897257
>>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>
0
 
LVL 11

Expert Comment

by:regbes
ID: 17897466
dinesh_bali,

run the following for each table

sp_helpindex TableName

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

Author Comment

by:dinesh_bali
ID: 17898286
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
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
ID: 17898306
run sql tuning advisor and check your query for index recommendation.
0
 
LVL 11

Expert Comment

by:regbes
ID: 17898338
dinesh_bali,

what one of these tables has the most data?

DC_DISTRIBUTIONCHANNELOPTIONS
CT_CUSTOMER
DC_DISTRIBUTIONCHANNEL
DC_DISTRIBUTIONCHANNELTYPE
CO_COUNTRY
CT_PREFERREDLANGUAGE
0
 

Author Comment

by:dinesh_bali
ID: 17898573
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
0
 

Author Comment

by:dinesh_bali
ID: 17898851
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?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17899721
Add *ONE* index, as follows:

CREATE INDEX CTCT_EMAIL_IND
    ON CT_CUSTOMER (CTCT_EMAIL)
0
 

Author Comment

by:dinesh_bali
ID: 17900050

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


0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question