sql server Getting Result fast

Question

Hi Good Morning,

I have a small Doubt in sql server. i am using a stored procedure to get Result in the form of tables

in that procedure i used just select queries

in each Query i am joining 5 tables .

when the procedure was executed it was showing result

But here the problem is it was taking time too much

Please provide me Solution to reduce the execute time of stored procedure.

Pradip Pandey · Answer

Change datatype from varchar to either int or bigint as per your requirement of the columns TableAId of TableA, TableAId & TableBId of TableB, TableAId, TableBId, TableCId of TableC and TableAId, TableBId, TableCId,TableDId of TableD. Create proper primary key constraint on each table and then foreign key constraint relationship between the tables. Hope it will help.

vinay kumar · Answer

Dear Pradeep HERE i changed the table names and every table contains Unique id CREATE TABLE [dbo] . [TableA] ( [Sno] [int] IDENTITY ( 1 , 1 ) NOT NULL, [TableAId] [varchar] ( 50 ) NULL, [TableA] [varchar] ( 50 ) NULL, ) CREATE TABLE [dbo] . [TableB] ( [Sno] [int] IDENTITY ( 1 , 1 ) NOT NULL, [TableAId] [varchar] ( 50 ) NULL, [TableBId] [varchar] ( 50 ) NULL, [TableB] [varchar] ( 300 ) NULL, ) CREATE TABLE [dbo] . [TableC] ( [Sno] [int] IDENTITY ( 1 , 1 ) NOT NULL, [TableAId] [varchar] ( 50 ) NULL, [TableBId] [varchar] ( 50 ) NULL, [TableCId] [varchar] ( 50 ) NULL, [TableC] [varchar] ( max ) NULL, [Entrydate] [smalldatetime] NULL, ) CREATE TABLE [dbo] . [TableD] ( [Sno] [int] IDENTITY ( 1 , 1 ) NOT NULL, [TableAId] [varchar] ( 50 ) NULL, [TableBId] [varchar] ( 50 ) NULL, [TableCId] [varchar] ( 50 ) NULL, [TableDId] [varchar] ( 50 ) NULL, [Image] [varbinary] ( max ) NULL, ) here in stored procedure i write this type of Query select A.TableAId,A.TableA,B.TableBId,B.TableB,C.TableCId, C.TableC,D.[TableDId],D.Image,C.Entrydate from TableA A inner join TableB B on A.TableAId=B.TableAId inner join TableC C on B.TableBId=C.TableBId inner join [TableD] D on C.TableCId=D.TableCId WHERE A.TableAId='10000' this is for just practise i want to know how can reduce the time of execution

Pradip Pandey · Answer

Can you send the table structure & joins that you are using to give more help. Hope it will help.

vinay kumar · Answer

Dear Pradeep i used indexes on this table it was just reduced 2 min time in all table i maintained the one unique key(like customer no)

Pradip Pandey · Answer

First of all, check table structure, indexes, primary key, foreign key constraints. If there is no any constraints defined on the table, define it. Also check data type of the column on which any constraint is defined. Similarly define indexes on the column of the table. Try to minimize your table join by using subquery. If join is must, then check the relationship between the tables. Hope it will help.