SmartList SLOOOOW when search filter applied

Question Status

Verified
Paul asked a question on 19 May 2017 2:13 PM

Hi:

I've run into a situation and am uncertain how to resolve it. I have a custom SmartList (based on a rather simple join of a couple of also simple views). When I view the unfiltered SmartList, it is reasonably quick and starts to fill the results window right away. However, when I search/filter on a column of interest looking for a particular ID, it takes "forever" (upwards of 5-10 minutes). 

I had a look at the actual query being run via DEXSQL.log and see that the generated query is roughly inline with what I'd have written myself. However, when the filter is applied, SmartList adds the following 'where' clause: WHERE (isnull(T2.[umStatementDocNo], 0) = '21750'). T2 is the table alias SmartList assigns, umStatementDocNo is the long integer column I mentioned and 21750 is my test ID obviously. 

When I copy the full query in SQL Server Management Studio, it runs like a dog, but when I change only the above 'where' clause to WHERE T2.[umStatementDocNo] = '21750' or WHERE T2.[umStatementDocNo] = 21750 (no quotes) it returns immediately. The superfluous isnull function is to blame. Why is SmartList adding it (especially as it is redundant; the field is non-nullable) and more importantly Can I force an alternate behaviour?

Thanks

Paul

Reply
Suggested Answer
Leslie Vail responded on 19 May 2017 5:26 PM

Hi Paul,

I would use a refreshable Excel report. You can program your filter however you want, and you can use a cell on the worksheet to be your filter if you want. I am impressed with your analysis of the query, I think you may be happier with the Excel report. Also, you can access the report from within the GP interface.

Kind regards,

Leslie

Reply
Verified Answer
Kirk Livermont responded on 19 May 2017 5:28 PM

Paul,

Alter the view in SQL and then use the SQL created view in Smartlist Builder.

I suspect the isnull is included by design to make smartlist creation as easy as possible for an end user with minimal table knowledge.

Kirk

Reply
Paul responded on 23 May 2017 7:29 AM

Kirk: It never even crossed my mind to try that, but it worked like a charm. Thanks!

Reply
Paul responded on 23 May 2017 8:16 AM

Leslie: Your suggestion intrigues me. I happened to come across a tech tip article on GPUG just last week and thought it looked promising... something new for my arsenal. Thanks for the suggestion! I'll definitely have a closer look.

Reply
Kirk Livermont responded on 23 May 2017 4:17 PM

Paul,

I'm glad we were able to get this sorted out for you.

I second Leslie regarding the refreshable excel report, especially the part about using a cell in Excel to feed the where clause of your query. These are very easy for end users to understand and in my organization have saved hundreds of man hours.

Regards,

Kirk

Reply
Verified Answer
Kirk Livermont responded on 19 May 2017 5:28 PM

Paul,

Alter the view in SQL and then use the SQL created view in Smartlist Builder.

I suspect the isnull is included by design to make smartlist creation as easy as possible for an end user with minimal table knowledge.

Kirk

Reply
Suggested Answer
Leslie Vail responded on 19 May 2017 5:26 PM

Hi Paul,

I would use a refreshable Excel report. You can program your filter however you want, and you can use a cell on the worksheet to be your filter if you want. I am impressed with your analysis of the query, I think you may be happier with the Excel report. Also, you can access the report from within the GP interface.

Kind regards,

Leslie

Reply