SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 index to a view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iosje
Starting Member

2 Posts

Posted - 12/12/2013 :  03:52:54  Show Profile  Reply with Quote
I have a SQL Server 2008 database met tables that contain BigInt fields. The interface I use for this database has problems with BigInt so I create a View that converts the BigInt data type to Varchar(8) or varchar(12), with the Convert function.
So the (indexed) ID of the table is BigInt, the ID of the view is varchar(12).
The Bigint fields dat contain dates like 20120312 will become varchar(8). Also I select parts of these string and concatenate them with other parts, so it will become '12-03-2012'.
Further I only select those records where this "date" field is not null.
Now the question: the performance seems to be worse, using this kind of views. Do I need to create a clustered index on these views?
I used to think that the view will just use the index on the table. But maybe it does not when you convert the data type?

Might it be better for the performance to convert the ID to Int instead of Varchar(12)?

Many thanks for your help,
Iosje

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/12/2013 :  10:43:09  Show Profile  Reply with Quote
If you can keep the basic data types that would be much better than converting numbers and dates to character. Will ALL your bigint values conform to the numeric range constraints of integer - now and for the foreseeable future? If so then I would try that first.

EDIT:
out of curiosity what interface are you using that can't deal with bigints?

Be One with the Optimizer
TG

Edited by - TG on 12/12/2013 10:45:05
Go to Top of Page

iosje
Starting Member

2 Posts

Posted - 12/12/2013 :  13:55:39  Show Profile  Reply with Quote
Hello TG,

Thanks for answering my mail. The interface is called iBridge and is programmed in Visual Basis. It's an interface to a graphic application called Analyst's Notebook.

Do you mean I should better convert the BigInt to Int than to varchar? I do think that in the foreseeable future the values would keep fitting into Int. But then I would still need the Convert function, right?
Does this mean I do or don't need indexes on my views?

Thanks,
Iosje
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/12/2013 :  14:54:36  Show Profile  Reply with Quote
Not sure - but if there is a convert function in the view definition on a column which is indexed in the underlying table it is certainly possible that sql will not use the index when selecting from the view. I'd say try it first.

Be One with the Optimizer
TG
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 12/17/2013 :  10:02:27  Show Profile  Reply with Quote
Views don't directly affect performance. If you compare the performance of a query without a view and one with a view, if the one with a view, when substituting the sql behind the view, is idenical to the sql of the query without the view, the performance is identical. So, if your performance is slower, that means you are not comparing apples to apples.

If you are using the view in such a way that the converted column is in a where clause, that is a problem, then SQL Server can't use the index because you are apply a function to that column first. You want to convert the column in your select list.

You could try re-creating the views as indexed views, with schemabinding.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000