Slow Running SQL Query?

Question

I've a SQL Server view. When I run a select statement against it as Select * from myView then it takes about 5 second to return me all rows (about12,000). But when I run a query like Select * from myView Where Field1 = 1 then it takes forever and do not returns any rows but keep executing. The strange thing is when I run the same query to filter another field, it executes faster i'e' Select * from myView Where Field2 = 1 . If someone can help me it will be really appreciated.

Answer 1

It's possible that Field2 is indexed where Field1 is not. You could try creating an index for Field1 but with only 2 values it probably won't buy you much. I'd try comparing how the Field1 column is defined as opposed to Field2...your comparison may be forcing each row to do an internal cast from integer to character (or vice versa) to perform the comparison.

Answer 2

once you're saying "which in production would usually take 20 minutes" does that propose that production isn't in a VM shopper? VMware (or one in all those technologies) is going to make your database SLOWER than it really is on actual hardware. The digital abstraction layers devour up an excellent variety of CPU and reminiscence, and each and every of the I/O usually is going by skill of the VM also. (There are some things you may do to help this, searching on your application.) a million. make sure to have as a lot reminiscence as in production 2. be sure sq. is tuned an identical become as in production 3. make sure to are utilising direct I/O for the disk(s) if utilising VMware, or similar function if no longer vmware 4. use perfmon on abode windows and local vmware performance approaches to help % out the precedence commence with those, and observe the way you're making out. you shold have the ability to do more advantageous than 2 hours.

Answer 3

is the table named myView? it is it a sql view, if it's a view, that means that it has stored the data before dor faster viewing, and the field that is not in the view, ie field1 will not get results becasue it can not compare the data.

Answer 4

once you're saying "which in production would usually take 20 minutes" does that propose that production isn't in a VM shopper? VMware (or one in all those technologies) is going to make your database SLOWER than it really is on actual hardware. The digital abstraction layers devour up an excellent variety of CPU and reminiscence, and each and every of the I/O usually is going by skill of the VM also. (There are some things you may do to help this, searching on your application.) a million. make sure to have as a lot reminiscence as in production 2. be sure sq. is tuned an identical become as in production 3. make sure to are utilising direct I/O for the disk(s) if utilising VMware, or similar function if no longer vmware 4. use perfmon on abode windows and local vmware performance approaches to help % out the precedence commence with those, and observe the way you're making out. you shold have the ability to do more advantageous than 2 hours.