sql server Getting Result fast

vinay kumar

13y1.8k

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pradip Pandey13y ago
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.
0
vinay kumar13y ago
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
0
Pradip Pandey13y ago
Can you send the table structure & joins that you are using to give more help.

Hope it will help.
0
vinay kumar13y ago
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)
0
Pradip Pandey13y ago
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.
0