SDAC Very Slow

jigesh · Post by **jigesh** » Mon 20 Dec 2010 20:52

Recently I upgraded by SDAC 3.70 to 5.0.00.3. Now my application takes 2 or more minutes to open. In the old version my application opened in about 5 seconds. I have not changed anything in my application. I need help in resolving this issue. I am using Delphi 7 and MS SQL 2005.
Please help.

From

Jigesh

AndreyZ · Post by **AndreyZ** » Tue 21 Dec 2010 10:12

Hello,

Please try to find out what operations cause such time loss: connection opening, query execution, or table opening.

jigesh · Post by **jigesh** » Wed 22 Dec 2010 16:26

There is one table that is opening very slow. It takes about 2 minutes. The same table in old version opened in 2 or 3 seconds. Please let me know what changes I have to do so that the table opens in 2 or 3 seconds like before.

With this new upgrade I am really dissappointed. It has slowed my whole app down.

Thanks

AndreyZ · Post by **AndreyZ** » Fri 24 Dec 2010 07:38

I could not reproduce the problem. Please send a script to create and fill a table to andreyz*devart*com.

jigesh · Post by **jigesh** » Mon 27 Dec 2010 17:43

If I create a new project then everything is fast. The problem is when I open my application created in SDAC version 3.70 in the new SDAC version 5.00.0.3 it is very slow. One of the table takes about 3 minutes to open which normally takes 5 sec when open in SDAC 3.70.

Please help. This slowness is creating a lot of problems from my upper management.

Thanks

Jigesh

AndreyZ · Post by **AndreyZ** » Tue 28 Dec 2010 08:19

Unfortunately, I still cannot reproduce the problem. To help you I need your script to create and fill a table. Also I need your options that you use in the TMSTable component. You can extract these options from the .dfm file as follows:

Code: Select all

  object MSTable: TMSTable
    TableName = 'dbo.test'
    Connection = MSConnection
    Left = 120
    Top = 40
  end

jigesh · Post by **jigesh** » Tue 28 Dec 2010 15:26

Here is the info of the table from the datamodule.dfm file.

Code: Select all

object tblSchedule: TMSTable
    TableName = 'Schedule_T'
    OrderFields = 'EquipID,RunDate,Sno'
    Connection = MSConn
    RefreshOptions = [roBeforeEdit]
    Active = True
    Left = 24
    Top = 168
    object tblScheduleSno: TIntegerField
      FieldName = 'Sno'
    end
    object tblScheduleJobN: TFloatField
      FieldName = 'JobN'
    end
    object tblScheduleJobDesc: TStringField
      DisplayWidth = 75
      FieldKind = fkLookup
      FieldName = 'JobDesc'
      LookupDataSet = qryOrders
      LookupKeyFields = 'JobNo'
      LookupResultField = 'Description'
      KeyFields = 'JobN'
      Size = 100
      Lookup = True
    end
    object tblScheduleEnteredDate: TDateTimeField
      FieldName = 'EnteredDate'
      Visible = False
    end
    object tblScheduleShipDate: TDateTimeField
      FieldName = 'ShipDate'
    end
    object tblScheduleRunDate: TDateTimeField
      FieldName = 'RunDate'
    end
    object tblScheduleItemID: TStringField
      FieldName = 'ItemID'
    end
    object tblScheduleQty: TFloatField
      FieldName = 'Qty'
    end
    object tblScheduleShtsDone: TFloatField
      FieldName = 'ShtsDone'
    end
    object tblScheduleDeptID: TIntegerField
      FieldName = 'DeptID'
      Visible = False
    end
    object tblScheduleEquipName: TStringField
      FieldKind = fkLookup
      FieldName = 'EquipName'
      LookupDataSet = qryEquipList
      LookupKeyFields = 'EquipID'
      LookupResultField = 'ShortName'
      KeyFields = 'EquipID'
      Size = 10
      Lookup = True
    end
    object tblScheduleEquipID: TIntegerField
      FieldName = 'EquipID'
      Visible = False
    end
    object tblScheduleSidePass: TStringField
      FieldName = 'SidePass'
      FixedChar = True
      Size = 10
    end
    object tblSchedulePass: TWordField
      FieldName = 'Pass'
      Visible = False
    end
    object tblScheduleColors: TStringField
      FieldName = 'Colors'
      Size = 210
    end
    object tblScheduleNotes: TStringField
      FieldName = 'Notes'
      Size = 500
    end
    object tblScheduleCtrlNo: TFloatField
      AutoGenerateValue = arAutoInc
      FieldName = 'CtrlNo'
      ReadOnly = True
    end
    object tblScheduleStatus: TStringField
      FieldName = 'Status'
      Size = 10
    end
    object tblScheduleStockID: TIntegerField
      FieldName = 'StockID'
    end
    object tblScheduleRunStatus: TStringField
      FieldKind = fkLookup
      FieldName = 'RunStatus'
      LookupDataSet = qryOrders
      LookupKeyFields = 'JobNo'
      LookupResultField = 'Priority'
      KeyFields = 'JobN'
      Size = 15
      Lookup = True
    end
    object tblSchedulePressOKType: TStringField
      FieldName = 'PressOKType'
    end
    object tblSchedulePressOKDate: TDateTimeField
      FieldName = 'PressOKDate'
    end
    object tblScheduleTack: TStringField
      FieldKind = fkLookup
      FieldName = 'Tack'
      LookupDataSet = qryOrders
      LookupKeyFields = 'JobNo'
      LookupResultField = 'TackYN'
      KeyFields = 'JobN'
      Size = 1
      Lookup = True
    end
    object tblScheduleFrontDoneYN: TStringField
      FieldName = 'FrontDoneYN'
      FixedChar = True
      Size = 1
    end
    object tblScheduleBackDoneYN: TStringField
      FieldName = 'BackDoneYN'
      FixedChar = True
      Size = 1
    end
    object tblScheduleTblSource: TStringField
      FieldName = 'TblSource'
      FixedChar = True
      Size = 1
    end
    object tblScheduleOrigQty: TFloatField
      FieldName = 'OrigQty'
    end
    object tblSchedulePLPFlag: TStringField
      FieldName = 'PLPFlag'
      FixedChar = True
      Size = 1
    end
    object tblSchedulelkUps: TIntegerField
      FieldKind = fkLookup
      FieldName = 'lkUps'
      LookupDataSet = qryOrders
      LookupKeyFields = 'JobNo'
      LookupResultField = 'NoUps'
      KeyFields = 'JobN'
      Lookup = True
    end
    object tblScheduleOnPressYN: TStringField
      FieldName = 'OnPressYN'
      FixedChar = True
      Size = 1
    end
    object tblSchedulePlatedYN: TStringField
      FieldName = 'PlatedYN'
      FixedChar = True
      Size = 1
    end
    object tblSchedulePrevPassStatus: TStringField
      FieldName = 'PrevPassStatus'
      FixedChar = True
      Size = 1
    end
    object tblScheduleColors_FB: TStringField
      FieldName = 'Colors_FB'
      FixedChar = True
      Size = 5
    end
    object tblSchedulelkComboID: TFloatField
      FieldKind = fkLookup
      FieldName = 'lkComboID'
      LookupDataSet = qryTktWShtList
      LookupKeyFields = 'JobNo'
      LookupResultField = 'ComboID'
      KeyFields = 'JobN'
      Lookup = True
    end
    object tblSchedulelkComboTargetYN: TStringField
      FieldKind = fkLookup
      FieldName = 'lkComboTargetYN'
      LookupDataSet = qryTktWShtList
      LookupKeyFields = 'JobNo'
      LookupResultField = 'TargetYN'
      KeyFields = 'JobN'
      Size = 1
      Lookup = True
    end
    object tblScheduleSide: TStringField
      FieldName = 'Side'
      FixedChar = True
      Size = 1
    end
  end
  object dsSchedule: TDataSource
    DataSet = tblSchedule
    Left = 88
    Top = 168
  end
end

Here is the script to create the table.

Code: Select all

USE [VCTData]
GO
/****** Object:  Table [dbo].[Schedule_T]    Script Date: 12/28/2010 09:24:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Schedule_T](
	[JobN] [numeric](18, 4) NULL,
	[EnteredDate] [datetime] NULL,
	[ShipDate] [datetime] NULL,
	[RunDate] [datetime] NULL,
	[Qty] [decimal](18, 0) NULL CONSTRAINT [DF_Schedule_T_Qty]  DEFAULT (0),
	[ShtsDone] [numeric](18, 0) NULL CONSTRAINT [DF_Schedule_T_ShtsDone]  DEFAULT (0),
	[StockID] [int] NULL CONSTRAINT [DF_Schedule_T_StockID]  DEFAULT (0),
	[ItemID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_ItemID]  DEFAULT (''),
	[DeptID] [int] NULL CONSTRAINT [DF_Schedule_T_DeptID]  DEFAULT (0),
	[EquipID] [int] NULL CONSTRAINT [DF_Schedule_T_EquipID]  DEFAULT (0),
	[Pass] [tinyint] NULL,
	[Side] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_Side]  DEFAULT (''),
	[Colors] [varchar](210) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_Colors]  DEFAULT (''),
	[SidePass] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_SidePass]  DEFAULT (''),
	[Notes] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_Notes]  DEFAULT (''),
	[Status] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_Status]  DEFAULT (''),
	[PressOKType] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_PressOKType]  DEFAULT (''),
	[PressOKDate] [datetime] NULL,
	[FrontDoneYN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_FrontDoneYN]  DEFAULT ('N'),
	[BackDoneYN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_BackDoneYN]  DEFAULT ('N'),
	[CtrlNo] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
	[Sno] [int] NULL,
	[TblSource] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_TblSource]  DEFAULT (''),
	[OrigQty] [numeric](18, 0) NULL CONSTRAINT [DF_Schedule_T_OrigQty]  DEFAULT (0),
	[PLPFlag] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_PLPFlag]  DEFAULT ('N'),
	[SchedAlertSwitch] [bit] NOT NULL CONSTRAINT [DF_Schedule_T_SchedAlertSwitch]  DEFAULT (0),
	[PlatedYN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Schedule_T_PlatedYN]  DEFAULT ('N'),
	[ScheduledBy] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Schedule_T_ScheduledBy]  DEFAULT (''),
	[OnPressYN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Schedule_T_OnPressYN]  DEFAULT ('N'),
	[ClickSeqNo] [int] NULL,
	[SDateFlag] [bit] NOT NULL CONSTRAINT [DF_Schedule_T_SDate]  DEFAULT (0),
	[SpecFlag] [bit] NOT NULL CONSTRAINT [DF_Schedule_T_SpecFlag]  DEFAULT (0),
	[PrevPassStatus] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Schedule_T_PrevPassStatus]  DEFAULT (''),
	[QtyFlag] [bit] NOT NULL CONSTRAINT [DF_Schedule_T_QtyFlag]  DEFAULT (0),
	[StkIDFlag] [bit] NOT NULL CONSTRAINT [DF_Schedule_T_StkIDFlag]  DEFAULT (0),
	[StkIDFlagB] [bit] NOT NULL CONSTRAINT [DF_Schedule_T_StkIDFlagB]  DEFAULT (0),
	[StkIDFlagM] [bit] NOT NULL CONSTRAINT [DF_Schedule_T_StkIDFlagM]  DEFAULT (0),
	[AddRemvPassFlag] [bit] NOT NULL CONSTRAINT [DF_Schedule_T_AddRemvPassFlag]  DEFAULT (0),
	[Colors_FB] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Schedule_T_Colors_FB]  DEFAULT (''),
 CONSTRAINT [PK_Schedule_T] PRIMARY KEY CLUSTERED 
(
	[CtrlNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

If you need more info please let me know.

Thanks

Jigesh

AndreyZ · Post by **AndreyZ** » Wed 29 Dec 2010 09:20

Please try the following:
1) set the tblSchedule.Options.CacheCalcFields option to True.
2) set the tblSchedule.Options.TrimFixedChar option to False.
If it doesn't help, remove all lookup fields from the tblSchedule table and check performance after it.

jigesh · Post by **jigesh** » Thu 30 Dec 2010 17:17

After making the changes you recomended, my table still takes a long time to open. In the dbmonitor program it shows that it takes 61 seconds.

For now I deleted the tblSchedule table from my datamodule and created a new TMSQuery called qrySchedule and setup all the lookup fields. Now my query opens up in 1 or 2 seconds.

If I have to recreate all my tables in my project for the new SDAC version 5.00.0.3 than it is a big problem. You have to come up with some solution so that my old project works like before in the new version of SDAC without any modification.

Thanks

Jigesh

AndreyZ · Post by **AndreyZ** » Mon 03 Jan 2011 13:46

Unfortunately, I still cannot reproduce the problem. Please try composing a small sample to demonstrate the problem and send it to andreyz*devart*com.

Lithium™ · Post by **Lithium™** » Fri 28 Jan 2011 11:32

Hi! Let me talk my experience.

I had similar problem several times in all versions of SDAC from 2.7 to now, but recreating of TMSQuery component doesn't affect.
Once TMSQuery had takes about 3-4min for SELECT-query, the same query in MS Management Studio takes about 2-5 sec.

I always could find decision. Here is several ways I can remember:
1. In some cases I find this occured because of TMSQuery put statements into Execute() proc, and there is a little chance runnig query away from Execute() may solve problem.
2. In some cases creating a stored proc on SQLServer for your query and getting results with using proc will solve problem.
3. In some cases (if SQL contains joins) TMSQuery using an incorrect ExecutionPlan (I don't no why), and adding hint "option (force order)" into SQL-statements solve problem.

I have no doubt that exist other problems and decisions. I'm get ready.

JensFudge · Post by **JensFudge** » Wed 09 Mar 2011 16:03

jigesh wrote:For now I deleted the tblSchedule table from my datamodule and created a new TMSQuery called qrySchedule and setup all the lookup fields. Now my query opens up in 1 or 2 seconds.

It's not really good to compare speed between a Table component and a query component..

I do agree that 2-3 minutes would be too long.
I noticed earlier in the thread that if you create everything from new, it works faster.
If thats a fact, there must be a difference between your existing tables, and new ones... Delphi has no way as to tell which is new and which is old.

I would try adding a table component (without deleting the existing one), set it up so it works faster.. Thats what I assume you can do according to earlier post.. And with the ViewAsText just compare the two...

You should spot a difference somewhere...

Hope it helps..

Best regards
Jens Fudge