Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
iosje
Starting Member
2 Posts |
 Posted - 2013-12-12 : 03:52:54
|
| 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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-12 : 10:43:09
|
| 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 OptimizerTG |
 |
|
|
iosje
Starting Member
2 Posts |
Posted - 2013-12-12 : 13:55:39
|
| 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 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-12 : 14:54:36
|
| 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 OptimizerTG |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-12-17 : 10:02:27
|
| 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. |
|
|
|
|
|
|
|
|
index to a view