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 |
|
mcolli00
Starting Member
19 Posts |
 Posted - 2010-12-02 : 11:23:52
|
| Hi all. I am new to tuning queries. I would like someone to comment what youwould do to speed this query up. My query plan is showing hash matches onworktable with wt_fb_fk (right outer), wt_fd_fk(right outer) and wt_rc_fk(inner join). Please help me to find a place to start. Thanks MCSelect * from worktable with(nolock) inner join dbo.funCleanCenter(3,1000) on wt_rc_fk = cc_pk left join dbo.funFacility(3,1000) on wt_fd_fk = fd_pk left join dbo.funBuilding(3,1000) on wt_fb_fk = fb_pk where wo_clnt_fk = 1000 and ((wt_fb_fk is not null and fb_pk is not null) or (wt_fb_fk is null and wt_fd_fk is not null and fd_pk is not null) or (wt_fb_fk is null and wt_fd_fk is null )) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-02 : 11:32:08
|
| Get rid of the select * and just return the columns you need.Then create covering indexes on the tables.Also probably get rid of the functions.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-02 : 11:41:50
|
| And get rid of NOLOCK = extremely dangerous unless you know, and program around, the consequences.(Although with a name like WORKTABLE it may be local to your process - but in that case locks won't be an issue as you will be the only user presumably?) |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-12-02 : 12:32:34
|
| I would also get rid of the proprietary function calls and replace them with queries that can be optimized. Non-SQL programmers use them because they make the code look like OO methods. The postfixes for PK and FK are a violation of basic data modeling. A data element name tells us WHAT the data elemtn is, and not HOW it is used in one particualr place.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
mcolli00
Starting Member
19 Posts |
Posted - 2010-12-02 : 15:38:44
|
| What do you mean by postfixes? Are you referring to using the primary and foreign keys on the joins or the not null logic? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-02 : 16:26:04
|
| "What do you mean by postfixes? Are you referring to using the primary and foreign keys on the joins or the not null logic? "The fact that your column names have "_fk" or "_pk" on the end. What happens if you decide to change the Primary Key - will you rename the appropriate columns to have "_PK" on the end to match the new Primary Key? And change all your Applications to match? |
|
|
|
mcolli00
Starting Member
19 Posts |
Posted - 2010-12-02 : 16:44:25
|
| Oh I see and good point! But too bad this database was here before me and that naming convention seems to be the standard. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-02 : 17:24:33
|
Change it then! The longer it is in place the more inconsistencies and workarounds that will evolve.Do you have "_V" or somesuch for a VIEW too? What happens when a Table is placed by a view, or vice versa? Same issue applies I reckon  |
|
|
|
|
|
|
tune query - first time tuner need advice please.