|
|
Posted by John Bell on 09/06/05 20:35
Hi
Just wondering why this is not stored as the separate fields if that is how
it is to be accessed?
John
"Matik" <marzec@sauron.xo.pl> wrote in message
news:1126025617.095282.258530@o13g2000cwo.googlegroups.com...
> 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]
|