Complex Query - stored procedure?

ALW

I have a very complex query that runs extremely slow and I want to speed it up. Is the best alternative to run it as a stored procedure and, if so, could some provide me with an example of a stored procedure they have created? Here is the query:

FIND Lead WHERE (Lead.ps_LeadStatus IN LoggedInRegularUser.ps_Filter_LeadStatus OR LoggedInRegularUser.ps_Filter_LeadStatus IS UNDEFINED) AND (Lead.pm_EmailCampaign IN LoggedInRegularUser.ps_Filter_EmailCampaign OR LoggedInRegularUser.ps_Filter_EmailCampaign IS UNDEFINED) AND (Lead.ps_PrimaryRep IN LoggedInRegularUser.ps_Filter_LeadRep OR LoggedInRegularUser.ps_Filter_LeadRep IS UNDEFINED) AND (Lead.ps_SecondaryRep IN LoggedInRegularUser.ps_Filter_SecondaryRep OR LoggedInRegularUser.ps_Filter_SecondaryRep IS UNDEFINED) AND (Lead.ps_Industry IN LoggedInRegularUser.Filter_Industry OR LoggedInRegularUser.Filter_Industry IS UNDEFINED) AND (Lead.ps_Function1 IN LoggedInRegularUser.Filter_Function OR LoggedInRegularUser.Filter_Function IS UNDEFINED) AND (Lead.CreditStatus IN LoggedInRegularUser.Filter_CreditStatus OR LoggedInRegularUser.Filter_CreditStatus IS UNDEFINED) AND (Lead.SalesStage IN LoggedInRegularUser.Filter_SalesStage OR LoggedInRegularUser.Filter_SalesStage IS UNDEFINED) AND (Lead.Probability IN LoggedInRegularUser.Filter_Probability OR LoggedInRegularUser.Filter_Probability IS UNDEFINED) AND (Lead.State IN LoggedInRegularUser.Filter_State OR LoggedInRegularUser.Filter_State IS UNDEFINED) AND (Lead.Country IN LoggedInRegularUser.Filter_Country OR LoggedInRegularUser.Filter_Country IS UNDEFINED) AND (Lead.ps_Region IN LoggedInRegularUser.Filter_Region OR LoggedInRegularUser.Filter_Region IS UNDEFINED) AND (Lead.City CONTAINS LoggedInRegularUser.Filter_City OR LoggedInRegularUser.Filter_City IS UNDEFINED) AND (Lead.ps_ParentCompany IN LoggedInRegularUser.Filter_ParentCompany OR LoggedInRegularUser.Filter_ParentCompany IS UNDEFINED) AND Lead.Active='Yes' AND (LoggedInRegularUser.Filter_DateUpdatedOptions IS UNDEFINED OR (Lead.DateUpdated>=LoggedInRegularUser.Filter_DateUpdatedFrom AND Lead.DateUpdated<=LoggedInRegularUser.Filter_DateUpdatedTo)) AND (LoggedInRegularUser.Filter_DateCreditOptions IS UNDEFINED OR (Lead.DateCreditUpdated>=LoggedInRegularUser.Filter_DateCreditFrom AND Lead.DateCreditUpdated<=LoggedInRegularUser.Filter_DateCreditTo)) AND (LoggedInRegularUser.Filter_DateEnteredOptions IS UNDEFINED OR (Lead.DateEntered>=LoggedInRegularUser.Filter_DateEnteredFrom AND Lead.DateEntered<=LoggedInRegularUser.Filter_DateEnteredTo))

aware_support

Stored procedures can increase the speed of a query dramatically - in some cases by orders of magnitude.

We have developed many ourselves while working on custom projects. You need to understand SQL in order to write them.

BLOMASKY

As aware support said, you DO have to know how to write efficient SQL queries. (while you can learn to write a SQL query in a few minutes, when there are a lot of tables (tables = Business Objects) being referenced and/or many conditions (which you have in your query), then there is some skill required in writing an efficient query.

Also, creating indexes (which can consist of multiple columns (columns = attributes) in SQL can dramatically affect the performance.

Bruce