|
Thats OK
Bob
Ashfield Consultants Ltd
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
thanks a lot
Sampiyon FENERBAHCE
|
|
|
|
|
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
|
|
|
|
|
Can't be done.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
When using SQL as the way you described, no.
However, if you use dynamic SQL, meaning you create a string that contains the SQL statement and execute it, this can be done.
The need to optimize rises from a bad design
|
|
|
|
|
What is mysql and what is ado.net
|
|
|
|
|
Hi,
MYSQL is a open source database management system.
For more details please visit the following link:
What is MySQL[^]
ADO.NET stands for ActiveX Data Objects for .NET. It refers to the suite of data access technologies used to manipulate databases. ADO.NET is part of the .NET Framework.
For more details please visit the following link:
What is ADO.Net[^]
Hope this helps  .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Hello Friends,
I have a table named Table1 and a column named CreationTime.
CreationTime has the datatype as datetime.
It gives the output as mm/dd/yyyy hh:mm:ss
But I want the output as hh:mm:ss
I am aware that I can use CAST or CONVERT function, but I am not getting a proper syntax for the same.
I would appreciate if anyone of you could help me to get the current syntax for what I wish to achieve.
Thank you,
notes4we
|
|
|
|
|
notes4we wrote: I am not getting a proper syntax for the same.
What does that mean?
SELECT CONVERT (varchar, CreationTime, 108) FROM Table1
ought to do it...
|
|
|
|
|
Thank you. I am able to get the desired output now.
|
|
|
|
|
|
?? ADO.NET is not a database.
|
|
|
|
|
Anyone has experience getting it to work? When I tried to configure an ODBC database source, there is a dropdown list that is supposed to have all existing (oracle) services defined on your machine. For me, this dropdown list contains unreadable characters. If I ignore this dropdown and type in my service name, it won't work either.
Does anyone have the same problem?
|
|
|
|
|
Hi fellow coders!
I'm in a desperate need for some help in the form of code to convert opc-data to ODBC-data, does anyone know how this has to be done.
My main problem is that I shall picksome specific parameters from the OPC stream and send that data to an ODBC source.
TIA 
Kind regards
Carsten
|
|
|
|
|
Are you meaning Open Packaging Conventions with OPC?
If you are, I believe that the only way is to write custom logic that reads the data, extracts the needed info and sends relevant data to target using ODBC.
The need to optimize rises from a bad design
|
|
|
|
|
Hi im very new to Ms Sql and have to create a view hat does the following using the northwind database:
Create views (c_v_best_sell_.....) to determine the best selling product based on Order Details. Your result should include the product_id, product name (Products), supplier_id, supplier_company_name (Suppliers) and the total units sold.
I can get it to display the best sold products amount in total using:
select max(T.total) from
(select productID, sum(Quantity * UnitPrice) as total from "Order Details" group by productID) T
or
Create View as c_v_best_sell
Select Max(T.total) as MaxSales
From(Select ProductID, sum(Quantity * UnitPrice) as total
From "Order Details"
group by "Order Details".ProductID )t
Any ideas on how to add the other colums?
|
|
|
|
|
Hi,
I have an application where i am selecting some records and updating and giving it back to the database. Now while i am updating these records, if another user is accessing the same table and same records, i want to restrict him to these records. Eg.. If i have selected some records with sr no. 1,2,3,4,5 and currently in a process in updating these records in an application, then i dont want these records to be updated or used by some other user at the same time.
Is it possible??
Sandeep Kumbhar
|
|
|
|
|
can you add a new column in the table which flag if it is being accessed ? and if it is then deny any other request to it.
so before updating the data, it will check if the flag is up or down. if it's down, change it to up and modify the data, commit, then change the flag to down. if the flag is up, then do nothing or show a message
|
|
|
|
|
i read somewhere that you can control record locking using different isolation levels 
|
|
|
|
|
By default, while the update is actually taking place nobody will have access to the record - that is known as a dirty read. I suspect what you actually are trying to describe is have user A and user A read the data, user A updates it and then user B updates it, losing user A's changes. This is a common scenario, frequently resolved by using either a timestamp column or a sequence number on the record which is checked before the update is done - if its changed then do not update.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
