help on performance tuning

N

.nLL

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
 
N

.nLL

i thing

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

Bob Milutinovic

.nLL said:
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
 
B

Bob Milutinovic

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

Bob Milutinovic said:
.nLL said:
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
 
A

Anthony Jones

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

Bob Milutinovic said:
.nLL said:
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. ;)
 
N

.nLL

thank you very much


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

Bob Milutinovic said:
.nLL said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top