Click here to Skip to main content
15,905,508 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have Two tables
SQL
CREATE TABLE [dbo].[Table_1](
    [col1] [varchar](20) NOT NULL,
    [col2] [varchar](20) NOT NULL,
    [col3] [varchar](20) NOT NULL,
    [col4] [varchar](100) NOT NULL,
    [col5] [varchar](100) NOT NULL,
    [col6] [varchar](100) NOT NULL,
    [col7] [varchar](20) NOT NULL,
    [col8] [varchar](40) NOT NULL,
    [col9] [varchar](20) NOT NULL,
    [Project] [varchar](20) NULL,
    [EmpNo] [numeric](18, 0) NULL
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [col1] ASC,
    [col2] ASC,
    [col3] ASC,
    [col4] ASC,
    [col5] ASC,
    [col6] ASC,
    [col7] ASC,
    [col8] ASC,
    [col9] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Table_2](
    [col1] [varchar](20) NOT NULL,
    [col2] [varchar](20) NOT NULL,
    [col3] [varchar](20) NOT NULL,
    [col4] [varchar](20) NOT NULL,
    [col5] [smallint] NULL,
    [col6] [numeric](18, 0) NULL,
    [Status] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
    [col1] ASC,
    [col2] ASC,
    [col3] ASC,
    [col4] 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_1 contains more than 40,000 records
table_2 contains around 5000 records

SQL
select A.col5 from Table_2 A inner join  Table_1 B on A.col1 = B.col1 AND A.col2 = B.col2 AND A.col3 = B.col3 and A.col4 = B.col4 AND B.EmpNo = 1256 AND A.Status=0


if i run above sql Query it takes around 3 to 4 seconds to run
how i can improve performance of the above sql Query
Posted
Comments
Tomas Takac 30-Oct-15 3:58am    
The clustered keys look scary. I wonder how do your inserts perform. Anyway, do you have a query plan?

Check out the execution plan / query optimizer. Check out the links for more info.
https://technet.microsoft.com/en-us/magazine/2007.11.sqlquery.aspx[^]
https://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/[^]

Good luck!
 
Share this answer
 
Your problem is your primary key, made of several varchar columns. As Tomas Takac, I suspect query times will be awful, as well as inserts (which may be even worse).
Use an integral single-column primary key; that does not prevent you from putting a unique index on all your varchar column, but at least you will have a chance to get decent performances.
 
Share this answer
 
Use proper relation ship between tables that will improve the performance, your query wise no problem use proper indexes to the table.

Check your execution plan, in which case it will give you detailed report.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900