Lekérdezések kategória bejegyzései

Amikor SQL független logika gyorsít

Volt egy esetem, amikor egy like-ot kellett felturbózni.
Az adatok így néztek ki (1 millio sor volt a táblában és folyamatosan nőtt):

A feladat a ‘kod11=2548796654’ részletet megkeresni az ‘url’ mezőben és listázni a hozzá tartozó sorokat.

A query adott volt, ezen kéne turbózni:

SELECT * FROM tábla WHERE url like '%kod11=2548796654%'

Kezdjük a szokás kérdéssel.
Kérdés: Miért nincs egy másik tábla is, ahol kulcs-érték párokba vannak rendezve ezek az adatok?
Válasz: Anno így lett elkészítve az adatbázis és most nincs idő refaktorálni.

Mit lehet tenni ilyenkor?

Az elméleti szakember itt felteszi a kezét, hogy ennyi volt, SQL-ben ennél többet nemigazán lehet tenni.
A tapasztalt szakember pedig már a kérdés pillanatában tudta a választ, ahogy azt is, hogy beszéltetni kell a fejlesztőt, mert lehet lesz kapaszkodó.

Jelen esetben két fontos információ hangzott még el a fejlesztőtől:
1.) A kod11 (2548796654) mindig egyedi érték lesz. (Megoldás: TOP 1)
2.) Mindig a friss információkra lesznek kíváncsiak (Megoldás: ORDER BY id DESC)

Egészítsük ki a lekérdezést:

SELECT TOP 1 * 
FROM tábla 
WHERE url like '%kod11=2548796654%' 
ORDER BY id DESC

Ez a megoldás 50X-es gyorsulást hozott és tartósan, mivel mindig garantáltan a tábla végéről kell adat.
Tehát mindegy, hogy mennyi sor van a táblában, a query mindig gyors lesz.

 

Mennyit tud segíteni egy kis beszélgetés?! 🙂

Reklámok

Viszlát SQL Server 2012

Elérkezett az idő, amikor az utolsó SQL Server 2012 verziójú szervereinknek is búcsút intünk. Egyikük 4 éve fut, szolgál ki és termeli a pénzt megállás nélkül. Ennyi idő alatt sikerült jól összebarátkozni vele és megállapítani a tényt, hogy ez a verzió igen stabilra sikeredett és határozottan alkalmas OLTP rendszerek kiszolgálására.

Nézzük meg ezt az időt számokban.

Összesítve 4 év adata:

  • Uptime: 4 év
  • Batch Requests total: 267 milliárd db
  • Index Searches total: 1444 milliárd db
  • Lock Requests total: 4086 milliárd db
  • Logins total: 54 milliárd db
  • Deadlocks total: 192 db
Lekérdezés:

use [master]
go

select
    [counter_name], [cntr_value]
from [sys].[dm_os_performance_counters]
where [counter_name] in ('batch requests/sec', 'Logins/sec', 'Index Searches/sec')
    or ([counter_name] = 'Number of Deadlocks/sec' and [instance_name] = '_Total')
    or ([counter_name] like 'lock_requ%' and [instance_name] = '_Total')
order by 1

Eredmény:

 

Másodpercenkénti átlagok:

  • Batch Requests/sec: 2082
  • Index Searches/sec: 11253
  • Lock Requests/sec : 31836
  • Logins/sec : 425

 

Lekérdezés:


use [master]
go

select
    cast([sqlserver_start_time] as date) as [sqlserver_start_time],
    datediff(year, [sqlserver_start_time], getdate()) as sql_server_uptime_in_year,
    serverproperty('ProductVersion') as product_version,
    serverproperty('Edition') as edition,
    left(@@VERSION, 31) as version_info,
    [c].[cntr_value] / datediff(second, [sqlserver_start_time], getdate()) as avg_batch_request_per_sec,
    [lr].[cntr_value] / datediff(second, [sqlserver_start_time], getdate()) as avg_lock_request_per_sec,
    [ls].[cntr_value] / datediff(second, [sqlserver_start_time], getdate()) as avg_login_per_sec,
    [dl].[cntr_value] as sum_deadlock,
    [iss].[cntr_value] / datediff(second, [sqlserver_start_time], getdate()) as avg_index_search_per_sec
from sys.[dm_os_sys_info] o
cross join sys.[dm_os_performance_counters] c
cross join sys.[dm_os_performance_counters] lr
cross join sys.[dm_os_performance_counters] ls
cross join sys.[dm_os_performance_counters] dl
cross join sys.[dm_os_performance_counters] iss
where c.[counter_name] like 'batch requests/sec%'
    and lr.[counter_name] like 'lock_requ%'
    and lr.[instance_name] = '_Total'
    and [ls].[counter_name] = 'Logins/sec'
    and [dl].[counter_name] = 'Number of Deadlocks/sec'
    and [dl].[instance_name] = '_Total'
    and iss.[counter_name] = 'Index Searches/sec'

Eredmény:

 

A számokból látszik, hogy bár nem lett túlhajtva a szerver, de azért nem is unatkozott.

 

Mennyi lyuk van a tábla id-jában?

Sokan szeretnék, ha egy tábla id-jában nem lennének lyukak. Ezt nem egyszerű megoldani, a legtöbb esetben nem is kell. Viszont ha kíváncsiak vagyunk, hogy mennyi ilyen van, akkor itt egy lekérdezés, amivel ezt megnézhetjük.


;with cte as (
    select
        id,
        id - row_number() over(order by id) as rn
from <table name> 

)

select
    row_number() over(order by (select null)) as group_id,
    min(id) as min_id,
    max(id) as max_id
from cte
group by rn
order by min_id

 

Demozzuk le

1.) Készítsünk egy táblát 100.000 sorral

drop table if exists #tmp 
go

select top 100000
	row_number() over(order by (select null)) as id
into #tmp
from sys.columns c1
cross join sys.columns c2
cross join sys.columns c3
cross join sys.columns c4
cross join sys.columns c5
go

2.) Töröljünk belőle, hogy tudjuk ellenőrizni a script-et

delete from #tmp 
where id between 1000 and 5000 
	or id between 10000 and 15000
	or id between 25000 and 35000
go

3.) Ellenőrizzük le

;with cte as (
	select
		id, 
		id - row_number() over(order by id) as rn
	from #tmp 
	
)


select
	row_number() over(order by (select null)) as group_id,
	min(id) as min_id, 
	max(id) as max_id
from cte 
group by rn
order by min_id

És az eredmény:

group_id             min_id               max_id
-------------------- -------------------- --------------------
1                    1                    999
2                    5001                 9999
3                    15001                24999
4                    35001                100000