SQL Server Performance

Query cause 100% CPU

Discussion in 'ALL SQL SERVER QUESTIONS' started by makepb, Mar 10, 2013.

  1. makepb New Member

    Actually i'm not sure what to do here. I'm using shared hosting and just received message from hoster that my MySql code, actually each query make 100% CPU. Thus if there are 3 queries then it is 300% CPU. Strange.

    Code that i used is well optimized for fast performance:
    Code:
    $query=" SELECT DISTINCT ip FROM visits AS r1
    JOIN (SELECT ROUND(
      RAND( ) * (
        SELECT MAX( id ) FROM visits)
      ) AS id
    ) AS r2
    WHERE r1.id >= r2.id
    AND counter='1'
    ORDER BY r1.id DESC
    LIMIT 10";
    $result=mysql_query($query);
    while($row =mysql_fetch_array($result)){
    $ip=$row['ip'];
    
    echo "$ip,";
    
    Another piece of code is almost similar:
    Code:
    $query=" SELECT DISTINCT ip FROM visits AS r1
    JOIN (SELECT ROUND(
      RAND( ) * (
        SELECT MAX( id ) FROM visits)
      ) AS id
    ) AS r2
    WHERE r1.id >= r2.id
    AND ip REGEXP '[A-Za-z0-9]' AND ip NOT REGEXP '\\([^\\)]*www.*\\)'
    ORDER BY r1.id ASC
    LIMIT 8";
    $result=mysql_query($query);
    while($row =mysql_fetch_array($result)){
    
    $ip=$row['ip'];
     
    echo "$ip,";
    
    Is there anything wrong with code?
    btw

    Table is very big. There are 176.000 rows as it means anything.

    Thanks for any input.
  2. Luis Martin Moderator

    SQL Server or MySQL?
  3. makepb New Member

    IT is MySql.
  4. Luis Martin Moderator

    This forum is SQL Server only. Try Mysql forums.
  5. Charandeep Nayyar New Member

    Use the below query to find out the query which uses maximum resources and time

    SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
    qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
    (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
    from
    sys.dm_exec_query_stats as qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
    ORDER BY qs.total_worker_time desc

Share This Page