help on performance tuning

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,
........................

........................
[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

Re: help on performance tuning

i thing

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



".nLL" 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,
> ........................
>
> ........................
> [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
>

Re: help on performance tuning

".nLL" 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,
> ........................
>
> ........................
> [lang310] [nvarchar](200) NULL
> ) ON [PRIMARY]
>



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

Re: help on performance tuning

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

"Bob Milutinovic" wrote in message
news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl...
> ".nLL" 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,
>> ........................
>>
>> ........................
>> [lang310] [nvarchar](200) NULL
>> ) ON [PRIMARY]
>>
>
>
>
> 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

Re: help on performance tuning

"Bob Milutinovic" 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" wrote in message
> news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl...
> > ".nLL" 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,
> >> ........................
> >>
> >> ........................
> >> [lang310] [nvarchar](200) NULL
> >> ) ON [PRIMARY]
> >>
> >
> >
> >
> > 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

Re: help on performance tuning

thank you very much


"Bob Milutinovic" 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" wrote in message
> news:O5ifHDrnIHA.1212@TK2MSFTNGP05.phx.gbl...
>> ".nLL" 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,
>>> ........................
>>>
>>> ........................
>>> [lang310] [nvarchar](200) NULL
>>> ) ON [PRIMARY]
>>>
>>
>>
>>
>> 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
>
>