Query running very slow

Question

Post reply

Query running very slow

peter-745387

SSC Rookie

Points: 29
More actions
May 27, 2008 at 12:47 am

#121661

Hello,
Database: sqlserver 2000/2005
There is two tables called TA and TB
TA containning 158529 record
TB containning 203404 record
TA's structure
(ID(pk),cat1,cat2,name1,name2)
TA's structure
(ID,location,amount)
for one ID in TA there are multiples record in TB
so one ID belongs to TA may be more than one existance find in TB
can you guide me how i write query so that it running very fast when I quried through my website.
Please help me.
Thanks
Peter

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Maybe. What do you want the query to return?

What indexes do you have on the tables?

Can you give us the table creation script (CREATE TABLE ...), index definitions, sample data, expected output and some idea of how fast you want the query to be?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

peter-745387 SSC Rookie Points: 29 More actions · Answer 2

Hello,

Thanks for reply

here is the details

Table creation script for TA

CREATE TABLE [dbo].[TA](

[ID] [nvarchar](13) NOT NULL,

[Cat1] [nvarchar](6) NULL,

[Cat2] [nvarchar](6) NULL,

[name1] [nvarchar](100) NULL,

[name2] [nvarchar](100) NULL,

CONSTRAINT [PK_cd_web_product] PRIMARY KEY CLUSTERED

(

[UPC] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table creation script for TB

CREATE TABLE [dbo].[cd_web_product_store](

[ID] [nvarchar](13) NOT NULL,

[Location] [nvarchar](40) NULL,

[amount] [money] NULL,

) ON [PRIMARY]

TA

IDCat1Cat2Name1 Name2

1001124Adams,Bryanany title

2001456lopez,jennifer any title

3005678any name any title

TB

IDLocationamount

1A001 90

1B001 90

1C001 60

1D001 80

2A001 110

2B001 120

3D001 130

4D001 140

…… …

If I want to search ‘adams’ then it will show all the records irrespective of location and values of location ‘A001’ appear first and there is only distinct record.

Thanks

Peter

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

No indexes on TableB?

What does your query look like?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Noticed that in both tables the column "ID" is defined as nvarchar(13) but the example data is "001", "002", "003". Is this column actually a numeric data type or that alphanumeric values have not been provided?

Note that performance is sensitive to the physical storage size, so if numeric is correct, changing to integer would result in a 10% reduction in all of I/O, CPU and memory resource needs.

SQL = Scarcely Qualifies as a Language

peter-745387 SSC Rookie Points: 29 More actions · Answer 5

Thanks for reply

yes both the ID field contains alphanumeric value.

like "001","002" etc

Thanks

Peter

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 6

In table A, the column UPC is the clustered primary key.

If you want to JOIN on ID column, both table A and table B should have an index over ID column.

N 56°04'39.16"
E 12°55'05.25"