QUERY tuning (datatype)

Hi experts ,

My developer sent me a query to review which is looks like

ALTER PROCEDURE [dbo].[PDFDocumentCreate]

     @DocumentID            VARCHAR  (100),

     @DocumentDate          CHAR     (10),

     @DocumentTime          CHAR     (10),
     @DocumentSize          INT,
     @PDFData               VARBINARY(MAX),
     @FileName              VARCHAR  (255),
     @OwnerID               VARCHAR  (50),
     @Title                 VARCHAR  (150),
     @CreationDate          CHAR     (10),
     @CreationTime          CHAR     (10),
     @Provisional           CHAR     (1),
     @PreviousVersionID     VARCHAR  (100),
     @SecondaryStorageURI NVARCHAR (150),
     @RecordID              BIGINT OUTPUT

Is that something i can change datatypes like he is using DocumentData for CHAR(10 ) .

please help me out thanks

more ▼

asked Apr 19 at 04:00 PM in Default

avatar image

190 5

What do you mean change datatypes? Meaning can you change the datatype the procedure is expecting?

Apr 19 at 04:18 PM JohnM
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Choosing the right datatype is very important for performance. From what I can see there are some strange datatypes that I should investigate further. Like DocumentDate, DocumentTime, CreationDate, CreationTime. Personally I think they should be stored in date vs time columns as it will save some bytes, but You should investigate how the parameters are used. If they are used in where clauses or join clauses, You have to make sure that the columns have the same datatype and lenght as the parameter, otherwise you'll get an implicit conversion that will cause performance issues

more ▼

answered Apr 24 at 05:54 PM

avatar image

Håkan Winther
16.6k 37 45 57

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 19 at 04:00 PM

Seen: 38 times

Last Updated: 4 days ago

Copyright 2016 Redgate Software. Privacy Policy