Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: help on performance tuning

Posted on 2008-04-14 23:31:52 by noone

got a multi languagel site.
my aproach was to create

CREATE TABLE [dbo].[languages](
[lang] [nvarchar](3) NOT NULL,
[lang1] [nvarchar](200) NULL,
[lang2] [nvarchar](200) NULL,
[lang3] [nvarchar](200) NULL,
[lang4] [nvarchar](200) NULL,
........................
<SNIP>
........................
[lang310] [nvarchar](200) NULL
) ON [PRIMARY]



have first row language identifier ( [lang] [nvarchar](3) NOT NULL,) and
rest as language source so lang2 to lang310 would have translations
if i want to add another lang i would just add another row with that
language

and my calssic asp function is

getLang("lang1","en")

------------------------------------------------------------ -----------------------------------

Function getLang(myInput,myLang)
Set MyCacheForLanguages = New DataCache
MyCacheForLanguages.ConnectionString = SqlConn
LanguagesSQL = "SELECT * FROM languages where lang='"&myLang&"'"
Set CacheRsForLanguages = MyCacheForLanguages.GetRecordset(LanguagesSQL)
Do While not CacheRsForLanguages.EOF
For i = 0 To CacheRsForLanguages.Fields.Count - 1
If CacheRsForLanguages.Fields.Item(i).name=myInput Then
getLang = CacheRsForLanguages.Fields.Item(i).value
Exit for
End if
Next
CacheRsForLanguages.MoveNext
Loop
Set CacheRsForLanguages = Nothing
Set MyCacheForLanguages = Nothing
End function


note New DataCache , it is memory caching class i use
------------------------------------------------------------ ----------------------------------


this combination works fine when i have couple of coulmns in language table
but i have over 300 columns and looping slowes server down.

i need another approach to make this combination faster. may be in function
or in select statement.

can anyone think of something faster without changing table stracture

Report this message

#2: Re: help on performance tuning

Posted on 2008-04-14 23:49:45 by noone

i thing

altering function as
getLang = CacheRsForLanguages.Fields.Item(myInput).value
and removing loop would do



".nLL" <noone@here.com> wrote in message
news:c3QMj.149467$833.115837@newsfe17.ams2...
> got a multi languagel site.
> my aproach was to create
>
> CREATE TABLE [dbo].[languages](
> [lang] [nvarchar](3) NOT NULL,
> [lang1] [nvarchar](200) NULL,
> [lang2] [nvarchar](200) NULL,
> [lang3] [nvarchar](200) NULL,
> [lang4] [nvarchar](200) NULL,
> ........................
> <SNIP>
> ........................
> [lang310] [nvarchar](200) NULL
> ) ON [PRIMARY]
>
>
>
> have first row language identifier ( [lang] [nvarchar](3) NOT NULL,) and
> rest as language source so lang2 to lang310 would have translations
> if i want to add another lang i would just add another row with that
> language
>
> and my calssic asp function is
>
> getLang("lang1","en")
>
> ------------------------------------------------------------ -----------------------------------
>
> Function getLang(myInput,myLang)
> Set MyCacheForLanguages = New DataCache
> MyCacheForLanguages.ConnectionString = SqlConn
> LanguagesSQL = "SELECT * FROM languages where lang='"&myLang&"'"
> Set CacheRsForLanguages = MyCacheForLanguages.GetRecordset(LanguagesSQL)
> Do While not CacheRsForLanguages.EOF
> For i = 0 To CacheRsForLanguages.Fields.Count - 1
> If CacheRsForLanguages.Fields.Item(i).name=myInput Then
> getLang = CacheRsForLanguages.Fields.Item(i).value
> Exit for
> End if
> Next
> CacheRsForLanguages.MoveNext
> Loop
> Set CacheRsForLanguages = Nothing
> Set MyCacheForLanguages = Nothing
> End function
>
>
> note New DataCache , it is memory caching class i use
> ------------------------------------------------------------ ----------------------------------
>
>
> this combination works fine when i have couple of coulmns in language
> table
> but i have over 300 columns and looping slowes server down.
>
> i need another approach to make this combination faster. may be in
> function
> or in select statement.
>
> can anyone think of something faster without changing table stracture
>

Report this message

#3: Re: help on performance tuning

Posted on 2008-04-15 06:25:45 by Bob Milutinovic

".nLL" <noone@here.com> wrote in message
news:c3QMj.149467$833.115837@newsfe17.ams2...
> got a multi languagel site.
> my aproach was to create
>
> CREATE TABLE [dbo].[languages](
> [lang] [nvarchar](3) NOT NULL,
> [lang1] [nvarchar](200) NULL,
> [lang2] [nvarchar](200) NULL,
> [lang3] [nvarchar](200) NULL,
> [lang4] [nvarchar](200) NULL,
> ........................
> <SNIP>
> ........................
> [lang310] [nvarchar](200) NULL
> ) ON [PRIMARY]
>

<snip>

How does one express profuse head-shaking using only 7-bit ASCII? Aaaaaargh!

Try applying some logic to the problem, and you'll find a far more elegant
'n' functional solution, infinitely easier to manage, and massively more
scalable.


CREATE TABLE Languages (
ID int IDENTITY(1,1) NOT NULL,
Lang nvarchar(5) NOT NULL,
Phrase nvarchar(200) NOT NULL)

......

FUNCTION getLang(iPhrase, sLang)
'--- assumes cDB is an already-established database connection
'--- input value sanitisation should be implemented!
sFunctionResult = ""
SET rsResult = cDB.Execute("SELECT Phrase FROM Languages WHERE ID=" &
iPhrase & " AND Lang='" & sLang & "'")
IF NOT rsResult.EOF THEN
sFunctionResult = rsResult("Phrase")
END IF
SET rsResult = NOTHING
getLang = sFunctionResult
END FUNCTION

Report this message

#4: Re: help on performance tuning

Posted on 2008-04-15 06:32:06 by Bob Milutinovic

Oops, force o' habit got in the way here; amended version follows at bottom.

"Bob Milutinovic" <cognicom@gmail.com> wrote in message
news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl...
> ".nLL" <noone@here.com> wrote in message
> news:c3QMj.149467$833.115837@newsfe17.ams2...
>> got a multi languagel site.
>> my aproach was to create
>>
>> CREATE TABLE [dbo].[languages](
>> [lang] [nvarchar](3) NOT NULL,
>> [lang1] [nvarchar](200) NULL,
>> [lang2] [nvarchar](200) NULL,
>> [lang3] [nvarchar](200) NULL,
>> [lang4] [nvarchar](200) NULL,
>> ........................
>> <SNIP>
>> ........................
>> [lang310] [nvarchar](200) NULL
>> ) ON [PRIMARY]
>>
>
> <snip>
>
> How does one express profuse head-shaking using only 7-bit ASCII?
> Aaaaaargh!
>
> Try applying some logic to the problem, and you'll find a far more elegant
> 'n' functional solution, infinitely easier to manage, and massively more
> scalable.
>

CREATE TABLE Languages (
ID int IDENTITY(1,1) NOT NULL,
PhraseID int NOT NULL,
Lang nvarchar(5) NOT NULL,
Phrase nvarchar(200) NOT NULL)

......

FUNCTION getLang(iPhrase, sLang)
'--- assumes cDB is an already-established database connection
'--- input value sanitisation should be implemented!
sFunctionResult = ""
SET rsResult = cDB.Execute("SELECT Phrase FROM Languages WHERE PhraseID="
& iPhrase & " AND Lang='" & sLang & "'")
IF NOT rsResult.EOF THEN
sFunctionResult = rsResult("Phrase")
END IF
SET rsResult = NOTHING
getLang = sFunctionResult
END FUNCTION

Report this message

#5: Re: help on performance tuning

Posted on 2008-04-15 08:57:53 by Anthony Jones

"Bob Milutinovic" <cognicom@gmail.com> wrote in message
news:e1P7qGrnIHA.4536@TK2MSFTNGP06.phx.gbl...
> Oops, force o' habit got in the way here; amended version follows at
bottom.
>
> "Bob Milutinovic" <cognicom@gmail.com> wrote in message
> news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl...
> > ".nLL" <noone@here.com> wrote in message
> > news:c3QMj.149467$833.115837@newsfe17.ams2...
> >> got a multi languagel site.
> >> my aproach was to create
> >>
> >> CREATE TABLE [dbo].[languages](
> >> [lang] [nvarchar](3) NOT NULL,
> >> [lang1] [nvarchar](200) NULL,
> >> [lang2] [nvarchar](200) NULL,
> >> [lang3] [nvarchar](200) NULL,
> >> [lang4] [nvarchar](200) NULL,
> >> ........................
> >> <SNIP>
> >> ........................
> >> [lang310] [nvarchar](200) NULL
> >> ) ON [PRIMARY]
> >>
> >
> > <snip>
> >
> > How does one express profuse head-shaking using only 7-bit ASCII?
> > Aaaaaargh!
> >
> > Try applying some logic to the problem, and you'll find a far more
elegant
> > 'n' functional solution, infinitely easier to manage, and massively more
> > scalable.
> >
>
> CREATE TABLE Languages (
> ID int IDENTITY(1,1) NOT NULL,
> PhraseID int NOT NULL,
> Lang nvarchar(5) NOT NULL,
> Phrase nvarchar(200) NOT NULL)
>


You can also ditch the ID column and place the PK across the PhraseID and
Lang columns. ;)

--
Anthony Jones - MVP ASP/ASP.NET

Report this message

#6: Re: help on performance tuning

Posted on 2008-04-15 19:39:18 by noone

thank you very much


"Bob Milutinovic" <cognicom@gmail.com> wrote in message
news:e1P7qGrnIHA.4536@TK2MSFTNGP06.phx.gbl...
> Oops, force o' habit got in the way here; amended version follows at
> bottom.
>
> "Bob Milutinovic" <cognicom@gmail.com> wrote in message
> news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl...
>> ".nLL" <noone@here.com> wrote in message
>> news:c3QMj.149467$833.115837@newsfe17.ams2...
>>> got a multi languagel site.
>>> my aproach was to create
>>>
>>> CREATE TABLE [dbo].[languages](
>>> [lang] [nvarchar](3) NOT NULL,
>>> [lang1] [nvarchar](200) NULL,
>>> [lang2] [nvarchar](200) NULL,
>>> [lang3] [nvarchar](200) NULL,
>>> [lang4] [nvarchar](200) NULL,
>>> ........................
>>> <SNIP>
>>> ........................
>>> [lang310] [nvarchar](200) NULL
>>> ) ON [PRIMARY]
>>>
>>
>> <snip>
>>
>> How does one express profuse head-shaking using only 7-bit ASCII?
>> Aaaaaargh!
>>
>> Try applying some logic to the problem, and you'll find a far more
>> elegant 'n' functional solution, infinitely easier to manage, and
>> massively more scalable.
>>
>
> CREATE TABLE Languages (
> ID int IDENTITY(1,1) NOT NULL,
> PhraseID int NOT NULL,
> Lang nvarchar(5) NOT NULL,
> Phrase nvarchar(200) NOT NULL)
>
> .....
>
> FUNCTION getLang(iPhrase, sLang)
> '--- assumes cDB is an already-established database connection
> '--- input value sanitisation should be implemented!
> sFunctionResult = ""
> SET rsResult = cDB.Execute("SELECT Phrase FROM Languages WHERE
> PhraseID=" & iPhrase & " AND Lang='" & sLang & "'")
> IF NOT rsResult.EOF THEN
> sFunctionResult = rsResult("Phrase")
> END IF
> SET rsResult = NOTHING
> getLang = sFunctionResult
> END FUNCTION
>
>

Report this message