SQL optimization

SmartList can perform optimized searches when used with SQL Server. To take advantage of this optimization, you must indicate that your new SmartList object is optimized for SQL, and provide several additional procedures that will be used by SmartList when it searches the data for the new object.

Since SQL Server is supported for all products, we recommend that you make your new SmartList objects optimized for SQL Server.

Indicating optimized objects

The Explorer_Optimize_For_SQL procedure is used to indicate that a specific SmartList object is optimized for SQL. This procedure should return the value true if the object is optimized, and false if it is not. When true is specified, SmartList will call additional procedures that are needed for implementing SQL-optimized searches.

The following is the Explorer_Optimize_For_SQL procedure from the sample integrating application. The sample defines the new Leads SmartList object. It provides the additional procedures needed to support SQL optimization, so it returns the value true when asked about whether the Leads SmartList object is optimized.

Procedure name: Explorer_Optimize_For_SQL

in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field;
inout boolean IO_Optimize;

{By default, assume not optimized}
IO_Optimize = false;

{Leads SmartList object is optimized}
if IN_Object_Dict_ID = IG_PROD_ID then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_LEADS then
		IO_Optimize = true;
	end if;
end if;

Retrieving SQL field information

To allow searching based on the columns you add to a SQL-optimized object, you must add the Explorer_Get_SQL_Field_Info procedure to your application. This procedure returns the following for each column:

Table technical name of the table that stores the data for the column
ID of the dictionary containing the table that stores the data for the column
Type of list field used for the column (if applicable)

This information is used when generating the SQL query used to search for matching records.

The following is the Explorer_Get_SQL_Field_Info procedure from the sample integrating application. For the Leads SmartList object defined by the sample, all of the fields for the columns come from the IG_Leads_MSTR table.

Procedure name: Explorer_Get_SQL_Field_Info

in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field;
inout string IO_Table_Name;
inout integer IO_Table_Dict_ID;
inout integer IO_DDL_Type;

if IN_Object_Dict_ID = IG_PROD_ID then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_LEADS then
		IO_Table_Name = technicalname(table IG_Leads_MSTR);
		IO_Table_Dict_ID = IG_PROD_ID;
		IO_DDL_Type = 1;
	end if;
end if;

Setting SQL join information

For SmartList objects optimized for SQL Server, it’s necessary to describe how any additional tables used for columns are related to the main table for the SmartList object. You will add the Explorer_Get_SQL_Join_Info procedure to your application to do this. In this procedure you will call the Explorer_Set_SQL_Join_Info procedure in the SmartList dictionary to define the join that describes how the main table for the SmartList object is related to any additional tables that contains data for the columns you’ve added. You will call this procedure once for each field that is part of the join.

Several types of joins are supported, but virtually all joins needed for SmartList are left-outer joins.

The following is the Explorer_Get_SQL_Join_Info procedure from the sample integrating application. In the sample, the main table for this object is the IG_Leads_MSTR table, and there are no additional tables used. The procedure creates an empty join to indicate no other tables are used.

Procedure name: Explorer_Get_SQL_Join_Info

in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in Explorer_INT_List IN_Field_Dict_ID;
in Explorer_INT_List IN_Field;
inout integer l_Doc_Status;

local string l_From_Table, l_From_Field, l_To_Table, l_To_Field;
local integer l_From_Table_Dict_ID, l_To_Table_Dict_ID, l_Join_Type, l_Exp_Error;

if IN_Object_Dict_ID = IG_PROD_ID then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_LEADS then
		l_From_Table = technicalname(table IG_Leads_MSTR);
		l_From_Table_Dict_ID = IG_PROD_ID;
		l_From_Field = "";
		l_To_Table = "";
		l_To_Table_Dict_ID = 0;
		l_To_Field = "";
		l_Join_Type = 0;

		call with name "Explorer_Set_SQL_Join_Info" in dictionary SMARTLIST,
			l_From_Table,
			l_From_Table_Dict_ID,
			l_From_Field,
			l_To_Table,
			l_To_Table_Dict_ID,
			l_To_Field,
			l_Join_Type,
			l_Exp_Error;
	end if;
end if;

Retrieving SQL query fields

When SmartList retrieves search results for a SQL-optimized object, it must be told which fields are the key fields that uniquely identify each row in the results. These fields will be included in the results set used to display the records that match the search criteria. You will add the Explorer_Get_SQL_Query_Fields procedure to your application to specify which fields to include in the key for your new SmartList object. The Explorer_Set_SQL_Query_Field procedure in the SmartList dictionary is used to specify each key field.

The following is the Explorer_Get_SQL_Query_Fields procedure for the sample integrating application. The sample defines the Leads SmartList object, which is optimized for SQL Server. The Lead ID field uniquely identifies each lead record, so this procedure specifies that the Lead ID will be included in the results set generated by the SmartList search.

Procedure name: Explorer_Get_SQL_Query_Fields

in integer IN_Object_Dict_ID;
in integer IN_Object_Type;

local integer l_Exp_Error, l_Dict, l_Sequence;
local string l_Table, l_Field;

if IN_Object_Dict_ID = IG_PROD_ID then
	if IN_Object_Type = SMARTLIST_OBJECTTYPE_LEADS then
		l_Table = technicalname(table IG_Leads_MSTR);
		l_Dict = IG_PROD_ID;
		l_Field = technicalname(field 'Lead ID');
		call with name "Explorer_Set_SQL_Query_Field" in dictionary SMARTLIST,
			l_Sequence,
			l_Table,
			l_Dict,
			l_Field,
			l_Exp_Error;
	end if;
end if;

Overriding field names

In special cases, the name used for a field will change depending upon the type of document the field is contained in. For example, this issue occurrs for inventory transactions. If the inventory document being displayed in SmartList comes from the “work” table, the document number field is named IV Document Number. However, if the document comes from the “history” table, the document number field is named Document Number. You will add the Explorer_Get_SQL_Override_Field_Name procedure to your application to handle these special cases. This procedure must return the appropriate field name to use, based on the document type.

If you don’t have field names that change based on the document type, this procedure should return the empty string. This causes the original field name to be used.

The following is the Explorer_Get_SQL_Override_Field_Name procedure for the sample integrating application. There are no field names that change based on the document type, so an empty string is returned for the field name.

Procedure name: Explorer_Get_SQL_Override_Field_Name

out string OUT_Field_Name;
in integer IN_Object_Dict_ID;
in integer IN_Object_Type;
in integer IN_Field_Dict_ID;
in integer IN_Field_Number;
in integer IN_Doc_Type;

clear OUT_Field_Name;