Viewing 15 posts - 46 through 60 (of 824 total)
Without seeing the execution plan I'm going to guess that your IDX_App index is not a covering index. With a nonclustered index unless it is very selective OR covering,...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 9:41 am
Dave, did you actually READ what I wrote?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 9:24 am
Take a look at the execution plan and see if the index is being utilized. If you need assistance, post the execution plan and I'll take a look at...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 9:10 am
A clustered index is ALWAYS unique. Sure you can make the clustered key columns non-unique, but in the background, SQL adds a four byte unique identifier to the key...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 25, 2009 at 8:33 am
Use the included columns, even though they take up the same amount of space whether they are included or part of the key, the index becomes a bit more expensive...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 24, 2009 at 10:22 am
In this case I'm pretty sure that the optimizer will just rewrite the subquery to be a join. I normally favor the join syntax just for stylistic reasons, but...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 24, 2009 at 9:54 am
Given that your search criteria all point to what are apparently bit type columns, you aren't going to get a nonclustered index to work without an index hint unless it's...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 24, 2009 at 9:42 am
That's a good point Paul.
If you can determine that the problem comes down to a particular statement you can use the OPTION (RECOMPILE), or you can modify the...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 24, 2009 at 8:32 am
The problem is determining what is "typical" and what parameter values are not... Also, if you recompile for an atypical parameter value, you must also recompile it "back" when...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 24, 2009 at 8:19 am
If storing the values to a file is an option, I'd set it up so that a new file is created every x minutes or when it gets to x...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 22, 2009 at 2:18 pm
It will not be the inserts that will be inefficient, but querying that data will most likely present the problems...
SQL, even on modest hardware can handle lots of simple inserts,...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 22, 2009 at 1:47 pm
What is the data type of the Products.ProdID column?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 22, 2009 at 12:04 pm
As Paul says, this can get rather complex, however I have a rule of thumb on these. If the procedure can be called with a wide range of parameter...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 22, 2009 at 11:58 am
I didn't notice that, but it's worth a try. Thanks
The extra columns are there and in that particular order because those are the filters (from most selective to least)...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 19, 2009 at 4:45 pm
I forgot to say, that I know what CXPacket waits are. I would expect a large parallel query to experience some CXPacket waits, I just don't know why in this...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 19, 2009 at 4:08 pm
Viewing 15 posts - 46 through 60 (of 824 total)