example_queries.sql

-- Example SQL queries for AMD GPU profiling analysis
-- Each query becomes a sheet in the output Excel file

-- @query: kernel_summary
-- @align: name
-- Summary of all GPU kernels with timing statistics
SELECT
    name,
    COUNT(*) as call_count,
    SUM(dur) / 1e6 as total_ms,
    AVG(dur) / 1e6 as avg_ms,
    MIN(dur) / 1e6 as min_ms,
    MAX(dur) / 1e6 as max_ms
FROM slice
WHERE category = 'hip' OR category = 'hsa' OR dur > 0
GROUP BY name
ORDER BY total_ms DESC;

-- @query: kernel_timeline
-- @align: name
-- Individual kernel executions in order
SELECT
    name,
    ts / 1e6 as start_ms,
    dur / 1e6 as dur_ms,
    track_id
FROM slice
WHERE dur > 0
ORDER BY ts;

-- @query: gpu_memory_ops
-- @align: name
-- Memory operations (copies, allocations)
SELECT
    name,
    COUNT(*) as count,
    SUM(dur) / 1e6 as total_ms,
    AVG(dur) / 1e6 as avg_ms
FROM slice
WHERE name LIKE '%copy%' OR name LIKE '%memcpy%' OR name LIKE '%Memcpy%'
   OR name LIKE '%alloc%' OR name LIKE '%Alloc%'
GROUP BY name
ORDER BY total_ms DESC;

-- @query: top_kernels
-- @align: name
-- Top 20 most time-consuming kernels
SELECT
    name,
    COUNT(*) as calls,
    SUM(dur) / 1e6 as total_ms,
    AVG(dur) / 1e6 as avg_ms
FROM slice
WHERE dur > 1000
GROUP BY name
ORDER BY total_ms DESC
LIMIT 20;

-- @query: track_summary
-- Summary of trace tracks (threads/streams)
SELECT
    t.name as track_name,
    COUNT(s.id) as event_count,
    SUM(s.dur) / 1e6 as total_dur_ms
FROM track t
LEFT JOIN slice s ON t.id = s.track_id
GROUP BY t.id, t.name
ORDER BY total_dur_ms DESC;