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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-21 : 07:56:56
|
| Noorie writes "What is the best way to optimise the following query:DECLARE CR04CARDS CURSOR FOR SELECT CC2301MAINACCIDE, CC2301ACCOUNTIDE2,CC2301ACCOUNTIDE3,CC2301ACCOUNTIDE4 FROM CA2301CARD WHERE (CC2301MAINACCIDE = :WM-ACCOUNTNUMBER OR CC2301ACCOUNTIDE2 = :WM-ACCOUNTNUMBER OR CC2301ACCOUNTIDE3 = :WM-ACCOUNTNUMBER OR CC2301ACCOUNTIDE4 = :WM-ACCOUNTNUMBER) AND CC2301CARDSTATUS <> '30' FOR UPDATE Thanks a million,Noorie" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-21 : 08:00:28
|
| Well, posting an Oracle question on a SQL Server website probably won't help you much. You can try here:http://dbforums.com/One or two recommendations I CAN make: normalize your table(s). You should not have multiple columns of the same type in the same table. And stop using cursors. No offense, but it's very difficult to optimize something that's fundamentally broken. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-04-21 : 13:28:43
|
quote:
Originally posted by robvolkOne or two recommendations I CAN make: normalize your table(s). You should not have multiple columns of the same type in the same table. And stop using cursors. No offense, but it's very difficult to optimize something that's fundamentally broken.
Cursors seem to work well in Oracle actually. go figure...-ec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-21 : 18:51:09
|
Well yeah, if you design your entire architecture around them, and you completely ignore relational theory and ANSI standards... |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-04-21 : 20:04:10
|
quote:
Originally posted by robvolk
Well yeah, if you design your entire architecture around them, and you completely ignore relational theory and ANSI standards...
well, it is really no different than Microsoft's implementation with TSQL. Other than the fact that Oracle cursors don't suck like TSQL cursors :)-ec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-21 : 20:38:29
|
It's no different, except that it took Oracle until 2001 to implement ANSI-style JOIN syntax, which I think was first introduced in SQL-89. And you gotta love those nested tables...cause they let you use nested cursors that much more often.  |
|
|
|
|
|
|
|
|
Optimising queries with multiple OR conditions