Database

Ashfield26-Sep-08 1:49

26-Sep-08 1:49

Odd, it works for me - although you get null in @vSQL if @X is null.

You will get a space each side of you value though.

Bob
Ashfield Consultants Ltd

TALHAKOSEN26-Sep-08 1:54

26-Sep-08 1:54

ITS THE CODE BELOW

DECLARE ankcev_cursor SCROLL CURSOR FOR
SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7'
OPEN ankcev_cursor

declare @vSQL varchar(1000), @numrows int
DECLARE @SNO INT;
DECLARE @KNO INT;
DECLARE @SONUC VARCHAR(10);
SET @SNO = 10;

WHILE @SNO < 83
BEGIN

FETCH NEXT FROM ankcev_cursor INTO @KNO

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SONUC=(SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND
KULLANICINO = @KNO AND SORUNO=@SNO)

SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ')'' VALUES('' '+ @sonuc+ ' '')'

Execute (@vSQL)
FETCH NEXT FROM ankcev_cursor INTO @KNO

END

SET @SNO=@SNO+1;

END

CLOSE ankcev_cursor
DEALLOCATE ankcev_cursor

Sampiyon FENERBAHCE

Ashfield26-Sep-08 2:18

26-Sep-08 2:18

It wasn't complaining about the select, it was the resuklt when you tried to exec it. You had too many quotes

try this

SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ') VALUES('' '+ @sonuc+ ' '')'

Your original selct gave this (I set @sonuc to f) :

INSERT INTO #rapor(S10)' VALUES(' f ')

rather than

INSERT INTO #rapor(S10) VALUES(' f ')

Bob
Ashfield Consultants Ltd

TALHAKOSEN26-Sep-08 2:22

26-Sep-08 2:22

thanx for helping bob

i saw now that i had a mistake

this is working below,
SELECT @vSQL = 'INSERT INTO #rapor(S'+ convert(varchar, @SNO) + ') VALUES('''+convert(varchar, @SONUC) +''')'

an by the way how can i see what value in my variable

i mean like c# Console.WriteLine(@vSQL) and it writes

INSERT INTO #rapor(S10) VALUES(' f ')

how can do with tsql

thanks again

Sampiyon FENERBAHCE

Ashfield26-Sep-08 3:18

one more )) pls variable doesnt increase ???

26-Sep-08 3:18

TALHAKOSEN wrote:
thanx for helping bob

No problem, always happy to help.

TALHAKOSEN wrote:
how can i see what value in my variable

If running in query analyser

print @vSQL

or simply

select @vSQL

Bob
Ashfield Consultants Ltd

TALHAKOSEN26-Sep-08 3:56

Re: one more )) pls variable doesnt increase ???

26-Sep-08 3:56

declare @vSQL varchar(1000)
DECLARE @SNO INT;
DECLARE @SNO2 INT;
DECLARE @KNO INT;
DECLARE @SONUC VARCHAR(10);
SET @SNO = 10;
SET @SNO2 = 10;

WHILE @SNO < 83
BEGIN
DECLARE ankcev_cursor SCROLL CURSOR FOR
SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7'
OPEN ankcev_cursor

FETCH NEXT FROM ankcev_cursor INTO @KNO

WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND KULLANICINO = @KNO AND SORUNO=@SNO
SET @SONUC=(SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND KULLANICINO = @KNO AND SORUNO=@SNO)
SELECT @vSQL = 'UPDATE #rapor SET S'+ convert(varchar, @SNO)+'='''+ convert(varchar, @SONUC)+''' where ID='''+ convert(varchar, @KNO) + ''''
Execute (@vSQL)
FETCH NEXT FROM ankcev_cursor INTO @KNO
END

CLOSE ankcev_cursor
DEALLOCATE ankcev_cursor
SET @SNO = @SNO+1;
END

PRINT @vSQL
PRINT @SNO ------ALWAYS WRITE S10 why stay same value ????

Sampiyon FENERBAHCE

Ashfield26-Sep-08 4:25

Re: one more )) pls variable doesnt increase ???

26-Sep-08 4:25

Don't know. I took your code and commented out all the bits that wouldn't run and it printed 83.

declare @vSQL varchar(1000)
DECLARE @SNO INT;
DECLARE @SNO2 INT;
DECLARE @KNO INT;
DECLARE @SONUC VARCHAR(10);
SET @SNO = 10;
SET @SNO2 = 10;


WHILE @SNO < 83
BEGIN
--	DECLARE ankcev_cursor SCROLL CURSOR FOR
--	SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7'
--	OPEN ankcev_cursor
--
--	FETCH NEXT FROM ankcev_cursor INTO @KNO
--
--	WHILE @@FETCH_STATUS = 0
--	BEGIN
--	--SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND KULLANICINO = @KNO AND SORUNO=@SNO
--		SET @SONUC=(SELECT CEVAPSIK FROM ANKETCEVAP WHERE ANKETID='7' AND KULLANICINO = @KNO AND SORUNO=@SNO)
--		SELECT @vSQL = 'UPDATE #rapor SET S'+ convert(varchar, @SNO)+'='''+ convert(varchar, @SONUC)+''' where ID='''+ convert(varchar, @KNO) + ''''
--		Execute (@vSQL)
--		FETCH NEXT FROM ankcev_cursor INTO @KNO
--	END
--
--	CLOSE ankcev_cursor
--	DEALLOCATE ankcev_cursor
	SET @SNO = @SNO+1;
END

PRINT @vSQL 
PRINT @SNO ------ALWAYS WRITE S10 why stay same value ????

Bob
Ashfield Consultants Ltd

TALHAKOSEN26-Sep-08 4:38

Re: one more )) pls variable doesnt increase ???

26-Sep-08 4:38

ok ok i see now my mistake

SELECT KULLANICINO FROM AnketCevap WHERE ANKETID='7' and <soruno> i forget to add this
so it takes to long times to return back ))

anyway thanks for your patience on me )) thank a lot...

Sampiyon FENERBAHCE

Ashfield26-Sep-08 8:18

26-Sep-08 8:18

Thats OK

Bob
Ashfield Consultants Ltd

Slow Random Products

Heylow26-Sep-08 1:27

Heylow

26-Sep-08 1:27

Hi
QUESTION:
Why is the code running so slow and what can I do to improve it?

THE CODE:

$currentTable = "SS_products";
$q1 = db_query("SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM ".
$currentTable) or die (db_error());

$row1 = db_fetch_row($q1);
$q2 = db_query("SELECT categoryID, name, default_picture FROM ".
$currentTable." WHERE categoryID=7 Limit $row1[0], 1") or die (db_error());

Background Infomation on the Code:
I have a website where I use a shopping cart system called ShopScript Premium. This system works more then fine with my current webhost BlueHost. But a problem arises when I add customized the code.

I have added to the website home page that every time the browser is refreshed 10 new random products will be the customers. The ShopScript system only offers products which are to be changed by the administrator manually. When the code for the random product is executed the website runs considerably slower then without the random products, although sometimes and does run with no problems and fast speed for a minute or so. Even if the user is using the other pages the website is still slow. I suspect this has to do with the fact that the ShopScript system uses a smarty engine, although that does not fully explain why.

In my database the table called has 1373 rows. I use the code above to randomly select one of the rows to be viewed on the website. According to my log on mySQL slow queries, the above mentioned code, is the source of the problems. The log states that the code uses 2 to 4 seconds query time. My webhost does not allow any single process taking more than 30 CPU seconds to run or if the total processes take more than 40 CPU seconds in any 60 second window to avoid server lockups. Such an event triggers a CPU Quota/Suspension Error, where my website is accessable for 5 to 10 minutes. This is a big problem as I lose customers this way.

On before hand
Thank you for your help

Re: Slow Random Products

SimulationofSai26-Sep-08 1:34

SimulationofSai

26-Sep-08 1:34

Count(*) is always slow. Try to use a different seed for generating your random number. If it's SQL Server 2005, you can use sys.sysindexes to get the rowcount.

Re: Slow Random Products

Ashfield26-Sep-08 1:58

26-Sep-08 1:58

If you don't need a really random record why not just select a record based on something like

select datediff(ms,getdate(), dateadd(hour, datepart(ss,getdate()),getdate()))

This may not be random enough, but you get the idea .

Bob
Ashfield Consultants Ltd

About reporting

TALHAKOSEN25-Sep-08 22:49

25-Sep-08 22:49

i need to get report from a table which has questionId field, studentId, inquiryId like that and there are 80 questions and 300 students replied this inquiry. I need a result like below (question=q)

inqueryId studentId q1 q2 q3 q4 q5 ..... q79 q80
7 123 a b b c d e c
7 245 b c d e e c c

i create temporary table has 80 question field but i cant fill it from my original table

Sampiyon FENERBAHCE

Re: About reporting

HemJoshi25-Sep-08 23:10

HemJoshi

25-Sep-08 23:10

U need to craete a dynamic query for inserting data in the table like

declare @Dynstr varchar(4000)

set @Dynstr ='update #TempDyamicWeek
SET col'+cast(@count1 as varchar(10)) +' =#TempDurByWeek.duration
from #TempDyamicWeek inner join #TempDurByWeek on
#TempDyamicWeek.userid= #TempDurByWeek.userid
and #TempDyamicWeek.type=#TempDurByWeek.type
and #TempDurByWeek.WeekEnding= '''+convert(varchar(30),@CurDateTo,121)+''''

exec (@Dynstr)

please coorect the colume name according to yours..

Thanks
Hem

Re: About reporting

TALHAKOSEN25-Sep-08 23:16

How can i change name of variable ?

25-Sep-08 23:16

thanks a lot

Sampiyon FENERBAHCE

TALHAKOSEN25-Sep-08 22:30

Re: How can i change name of variable ?

25-Sep-08 22:30

Hi all,

I want to learn that, can i change name of variable, i mean i wanna have
@s1, @s2, @s3 ... @s20 but i dont wanna write it wanna use

WHILE X<20
BEGIN
DECLARE @SX
X=X+1;
END

CAN I DO THAT pls help

Sampiyon FENERBAHCE

Ashfield25-Sep-08 22:40