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, 2017 at 04:00 PM in Default

avatar image

240 1 10

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

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

2 answers: 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, 2017 at 05:54 PM

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left

I agree with Håkan Winther on this especially the part about making the input variables match the data types in the underlying table (especially when this appears to be a C.R.U.D. procedure to create new rows in a table) unless the stored procedure does actually do some checking/error trapping or some other process that requires either date/time as a character based entry (which could probably be avoided if the correct temporal data types were actually used, which would make it self checking).

I'll also add that if the underlying table is, in fact, using CHAR(10) for any kind of date or time, then that constitutes a serious design flaw that will have an impact on data integrity (you could enter non-date/time info in the columns) and any kind of criteria that looks for date/time ranges, sorting, etc, etc, and the related performance of all such things even if ISO formatting is used religiously.

more ▼

answered Apr 30, 2017 at 03:14 PM

avatar image

Jeff Moden
2.3k 3 7 13

Totally agree on the design flaw. Right datatype is everything in database design, otherwise you'll end up with a disaster.

May 01, 2017 at 07:09 PM Håkan Winther
(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, 2017 at 04:00 PM

Seen: 82 times

Last Updated: May 01, 2017 at 07:09 PM

Copyright 2018 Redgate Software. Privacy Policy