Hi to all!
I developing multi-tabed-interface that working with MSSQL2008R2. One type of form (document) can be opened manny times / multipe instances and change ih one document must be visibile on other documents /forms. Changes maden on some form in mti application, or changes of other user/comp/client. I refreshing data on adding after posting changes and after delete, and generally on setting form focus/change active form.
TMSChangeNottification doesn't provide support for views and SQL2008, and other important (to me) features. Other suitable solution is RefreshQuick, but this sollution has one big problem - very slow refreshing/fetching when is CheckDeleted set to True (i need check for deletions)
At form is placed 3 queryes.
1.st query is for navigation trough all data in "search grid", it fetching 25 rows, work's only select of 4 fields (GUID-ID, timestamp named synctime, and some attribute fields) and is coupled to datasource and db navigator. It works with cca. 12000 rows selected form view.
2.nd query is for editing. It is coupled to 1.st query (MasterSource and WHERE id = :id in select SQL). It fetching all rows/only one row at time. This sql works with table (that-s part of multi joined view selected in 1.st query). Foreign tables was coupled "mannualy" with tms lookup components, I don't use Delphi's lookup fields or any calculations at fields.
3.rd query is select of all fields and all rows from view (same view from 1.st query, but with all fields, not only 4). opened and used for searching and reporting only when needed (very lov freqention of use).
1.st and 2.nd query was seted key fields to ID (id=pk, guid), all fields are created in fielddef lists, CacheCalcFields = True, CursorUpdate=True, DMLRefresh=True, ReturnParams=True.
I tried ordering on server, and local ordering but with no sucess. My gelerall problem is slowness of RefreshQuick(True). Refreshing with checking deleted, and same refreshing after deleting record's is werry slow. DBMonitor loging statemens in duration in parts of seconds, buth refresh really fetching data from 1.st query take cca. 5 seconds.
I don't use cached updates. I need refreshing of data before after changing, before edit, after delete on own datasets, and refresh of changes on focusing/activating of form. As I Say'd refreshQuick does work, buth werry slow (useless slowly).
Do you have any sugsetion or recomendation for use of RefreshQuick and generally for refreshing changes.
Refresh quick or some other changes nottification
Sample of table and view that's selected in queryes:
/****** Object: Table [dbo].[FakturiranjeRoba] Script Date: 03/06/2012 07:38:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FakturiranjeRoba](
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Oznaka] [nvarchar](50) NULL,
[Naziv] [nvarchar](50) NOT NULL,
[Naziv2] [nvarchar](50) NULL,
.... other 50 fields
[SyncTime] [timestamp] NULL,
CONSTRAINT [PK_FakturiranjeRoba] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[FakturiranjeRoba] WITH CHECK ADD CONSTRAINT [FK_FakturiranjeRoba_CarineZaglavlja] FOREIGN KEY([CarinaID])
REFERENCES [dbo].[CarineZaglavlja] ([ID])
... other constraints
/****** Object: View [dbo].[v_FakturiranjeRoba] Script Date: 03/06/2012 07:38:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_FakturiranjeRoba]
AS
SELECT TOP (100) PERCENT dbo.FakturiranjeRoba.ID, dbo.FakturiranjeRoba.Oznaka, dbo.FakturiranjeRoba.Naziv, dbo.FakturiranjeRoba.Naziv2, dbo.FakturiranjeRoba.Naziv3,
...other fields,
dbo.FakturiranjeRoba.SyncTime
FROM dbo.FakturiranjeRoba LEFT OUTER JOIN
dbo.v_FakturiranjeGrupeProizvoda ON dbo.FakturiranjeRoba.GrupaProizvodaID = dbo.v_FakturiranjeGrupeProizvoda.ID LEFT OUTER JOIN
dbo.v_PoslovniPartneri ON dbo.FakturiranjeRoba.DobavljacID = dbo.v_PoslovniPartneri.ID LEFT OUTER JOIN
... other joins...
LEFT OUTER JOIN
dbo.v_FakturiranjeJediniceMjere AS v_FakturiranjeJediniceMjere_1 ON dbo.FakturiranjeRoba.JedinicaMjereID = v_FakturiranjeJediniceMjere_1.ID
ORDER BY dbo.FakturiranjeRoba.ID
1.st query SQL:
select id, oznaka, naziv, barcode, SyncTime from v_fakturiranjeroba
&UvjetFiltera
order by oznaka, naziv;
(&UvjetFiltera can be used for dinamyc inserting of WHERE clasue, not even used)
2.nd query SQL:
select * from fakturiranjeRoba
WHERE id = :id
Insert:
INSERT INTO fakturiranjeRoba
(ID, Oznaka, Naziv, Naziv2, Naziv3, JedinicaMjereID, BarCode, KataloskaOznaka, KlasifikatorID,...other fields..., ProizvodjacAdresa)
VALUES
(:ID, :Oznaka, :Naziv, :Naziv2, :Naziv3, :JedinicaMjereID, :BarCode, :KataloskaOznaka, :KlasifikatorID, ...other params... , :ProizvodjacAdresa)
Update:
UPDATE fakturiranjeRoba
SET
ID = :ID, Oznaka = :Oznaka, Naziv = :Naziv, ...other field/value pairs..., ProizvodjacAdresa = :ProizvodjacAdresa
WHERE
ID = :Old_ID
Delete:
DELETE FROM fakturiranjeRoba
WHERE
ID = :Old_ID
Refresh:
SELECT ID, Oznaka, Naziv, Naziv2, Naziv3, JedinicaMjereID, BarCode, KataloskaOznaka, KlasifikatorID, ...other fields..., SyncTime, SyncTime FROM fakturiranjeRoba
WHERE
ID = :ID
3.rd query SQL:
select * from v_fakturiranjeroba
&UvjetFiltera
order by oznaka, naziv;
Cursor are: ctDefaultResultSet
On inserting/ new ID is generated at code, in OnNewRecord event handler and asociated with DataSet.FieldByName('ID').AsString
Thanks!
With best reg... Ivan
/****** Object: Table [dbo].[FakturiranjeRoba] Script Date: 03/06/2012 07:38:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FakturiranjeRoba](
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Oznaka] [nvarchar](50) NULL,
[Naziv] [nvarchar](50) NOT NULL,
[Naziv2] [nvarchar](50) NULL,
.... other 50 fields
[SyncTime] [timestamp] NULL,
CONSTRAINT [PK_FakturiranjeRoba] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[FakturiranjeRoba] WITH CHECK ADD CONSTRAINT [FK_FakturiranjeRoba_CarineZaglavlja] FOREIGN KEY([CarinaID])
REFERENCES [dbo].[CarineZaglavlja] ([ID])
... other constraints
/****** Object: View [dbo].[v_FakturiranjeRoba] Script Date: 03/06/2012 07:38:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_FakturiranjeRoba]
AS
SELECT TOP (100) PERCENT dbo.FakturiranjeRoba.ID, dbo.FakturiranjeRoba.Oznaka, dbo.FakturiranjeRoba.Naziv, dbo.FakturiranjeRoba.Naziv2, dbo.FakturiranjeRoba.Naziv3,
...other fields,
dbo.FakturiranjeRoba.SyncTime
FROM dbo.FakturiranjeRoba LEFT OUTER JOIN
dbo.v_FakturiranjeGrupeProizvoda ON dbo.FakturiranjeRoba.GrupaProizvodaID = dbo.v_FakturiranjeGrupeProizvoda.ID LEFT OUTER JOIN
dbo.v_PoslovniPartneri ON dbo.FakturiranjeRoba.DobavljacID = dbo.v_PoslovniPartneri.ID LEFT OUTER JOIN
... other joins...
LEFT OUTER JOIN
dbo.v_FakturiranjeJediniceMjere AS v_FakturiranjeJediniceMjere_1 ON dbo.FakturiranjeRoba.JedinicaMjereID = v_FakturiranjeJediniceMjere_1.ID
ORDER BY dbo.FakturiranjeRoba.ID
1.st query SQL:
select id, oznaka, naziv, barcode, SyncTime from v_fakturiranjeroba
&UvjetFiltera
order by oznaka, naziv;
(&UvjetFiltera can be used for dinamyc inserting of WHERE clasue, not even used)
2.nd query SQL:
select * from fakturiranjeRoba
WHERE id = :id
Insert:
INSERT INTO fakturiranjeRoba
(ID, Oznaka, Naziv, Naziv2, Naziv3, JedinicaMjereID, BarCode, KataloskaOznaka, KlasifikatorID,...other fields..., ProizvodjacAdresa)
VALUES
(:ID, :Oznaka, :Naziv, :Naziv2, :Naziv3, :JedinicaMjereID, :BarCode, :KataloskaOznaka, :KlasifikatorID, ...other params... , :ProizvodjacAdresa)
Update:
UPDATE fakturiranjeRoba
SET
ID = :ID, Oznaka = :Oznaka, Naziv = :Naziv, ...other field/value pairs..., ProizvodjacAdresa = :ProizvodjacAdresa
WHERE
ID = :Old_ID
Delete:
DELETE FROM fakturiranjeRoba
WHERE
ID = :Old_ID
Refresh:
SELECT ID, Oznaka, Naziv, Naziv2, Naziv3, JedinicaMjereID, BarCode, KataloskaOznaka, KlasifikatorID, ...other fields..., SyncTime, SyncTime FROM fakturiranjeRoba
WHERE
ID = :ID
3.rd query SQL:
select * from v_fakturiranjeroba
&UvjetFiltera
order by oznaka, naziv;
Cursor are: ctDefaultResultSet
On inserting/ new ID is generated at code, in OnNewRecord event handler and asociated with DataSet.FieldByName('ID').AsString
Thanks!
With best reg... Ivan
-
AndreyZ