|
|
Posted by Matik on 09/06/05 19:53
Hello all,
I've following problem. Please forgive me not posting script, but I
think it won't help anyway.
I've a table, which is quite big (over 5 milions records). Now, this
table contains one field (varchar[100]), which contains some data in
the chain.
Now, there is a view on this table, to present the data to user. The
problem is, in this view need to be displayed some data from this one
large field (using substring function or inline function returning
value).
User in the application, is able to filter and sort threw this fields.
Now, the whole situation starts to be more complicated, if I would like
combine this table, with another one, where is one additional much mor
larger field, from which I need to select data in the same way.
Problem is: it takes TO LONG to select the data according to user
request (user access view, not table direct)
Now the question:
- using this substring (as in example) is agood solution, or beter to
do a inline function which will return me the part of this dataset
(probably there is no difference)
- will it be much faster, if i could add some fields in to
Source_Table, containing also varchar data, but only this part which
I'm interested in and binde these fields in view instead off using
substring function?
Small example:
CREATE TABLE [dbo].[Source_Table] (
[CID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[MSrepl_tran_version] uniqueidentifier ROWGUIDCOL NULL ,
[Date_Event] [datetime] NOT NULL ,
[mama_id] [varchar] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[mama_type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[tata_id] [varchar] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[tata_type] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[loc_id] [nvarchar] (64) COLLATE Latin1_General_CI_AS NOT NULL ,
[sn_no] [smallint] NOT NULL ,
[tel_type] [smallint] NULL ,
[loc_status] [smallint] NULL ,
[sq_break] [bit] NULL ,
[cmpl_data] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[fk_cmpl_erp_data] [numeric](18, 0) NULL ,
[erp_dynia] [bigint] NULL
) ON [PRIMARY]
GO
create view VIEW_AllData
as
select top 100 percent
isnull(substring(RODZ.cmpl_data,27,10),'-') as ASO_NO,
(RODZ.mama_type + RODZ.mama_Id) as MAMA,
isnull(substring(RODZ.cmpl_data,45,5),'-') as MI,
isnull(substring(RODZ.cmpl_data,57,3),'-') as ctl_EC,
isnull(substring(RODZ.cmpl_data,60,3),'-') as ctl_IC,
RODZ.Date_Event as time_time,
RODZ.sn_no as SN
FROM
Source_Table RODZ with (nolock)
go
Thanks in advance
Mateusz
Navigation:
[Reply to this message]
|