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
Solved

Fetching data is taking time from database

Posted on 2006-11-08
16
202 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

856 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