ایک ایس کیو ایل تحقیقات کی کہانی

پچھلے دسمبر میں مجھے VWO سپورٹ ٹیم سے ایک دلچسپ بگ رپورٹ موصول ہوئی۔ ایک بڑے کارپوریٹ کلائنٹ کے لیے تجزیاتی رپورٹس میں سے ایک کے لیے لوڈنگ کا وقت ممنوعہ لگتا تھا۔ اور چونکہ یہ میری ذمہ داری کا علاقہ ہے اس لیے میں نے فوری طور پر اس مسئلے کو حل کرنے پر توجہ دی۔

پس منظر

یہ واضح کرنے کے لیے کہ میں کس بارے میں بات کر رہا ہوں، میں آپ کو VWO کے بارے میں تھوڑا سا بتاؤں گا۔ یہ ایک ایسا پلیٹ فارم ہے جس کے ساتھ آپ اپنی ویب سائٹس پر مختلف ٹارگٹڈ مہمات شروع کر سکتے ہیں: A/B تجربات کریں، وزٹرز اور تبادلوں کو ٹریک کریں، سیلز فنل کا تجزیہ کریں، ہیٹ میپس ڈسپلے کریں اور وزٹ ریکارڈنگ چلائیں۔

لیکن پلیٹ فارم کے بارے میں سب سے اہم چیز رپورٹنگ ہے۔ مندرجہ بالا تمام افعال ایک دوسرے سے جڑے ہوئے ہیں۔ اور کارپوریٹ کلائنٹس کے لیے، معلومات کی ایک بڑی مقدار کسی طاقتور پلیٹ فارم کے بغیر بے کار ہو گی جو اسے تجزیاتی شکل میں پیش کرتا ہے۔

پلیٹ فارم کا استعمال کرتے ہوئے، آپ بڑے ڈیٹا سیٹ پر بے ترتیب سوال کر سکتے ہیں۔ یہاں ایک سادہ مثال ہے:

صفحہ "abc.com" پر <date d1> سے <date d2> تک کے تمام کلکس ان لوگوں کے لیے دکھائیں جنہوں نے کروم استعمال کیا یا (یورپ میں واقع اور آئی فون استعمال کیا)

بولین آپریٹرز پر توجہ دیں۔ وہ نمونے حاصل کرنے کے لیے من مانی پیچیدہ سوالات کرنے کے لیے سوالات کے انٹرفیس میں کلائنٹس کے لیے دستیاب ہیں۔

سست درخواست

زیربحث مؤکل کچھ ایسا کرنے کی کوشش کر رہا تھا جو بدیہی طور پر تیزی سے کام کرے:

ان صارفین کے لیے تمام سیشن ریکارڈ دکھائیں جنہوں نے "/نوکریوں" والے URL کے ساتھ کسی بھی صفحے کا دورہ کیا

اس سائٹ پر ٹریفک کا ایک ٹن تھا اور ہم صرف اس کے لیے ایک ملین سے زیادہ منفرد URLs اسٹور کر رہے تھے۔ اور وہ کافی آسان URL ٹیمپلیٹ تلاش کرنا چاہتے تھے جو ان کے کاروباری ماڈل سے متعلق ہو۔

ابتدائی تفتیش

آئیے ایک نظر ڈالتے ہیں کہ ڈیٹا بیس میں کیا ہو رہا ہے۔ ذیل میں اصل سست SQL استفسار ہے:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

اور اوقات یہ ہیں:

منصوبہ بند وقت: 1.480 ms عمل درآمد کا وقت: 1431924.650 ms

استفسار نے 150 ہزار قطاریں کرال کیں۔ استفسار کے منصوبہ ساز نے کچھ دلچسپ تفصیلات دکھائیں، لیکن کوئی واضح رکاوٹ نہیں۔

آئیے درخواست کا مزید مطالعہ کرتے ہیں۔ جیسا کہ آپ دیکھ سکتے ہیں، وہ کرتا ہے۔ JOIN تین میزیں:

1. سیشن: سیشن کی معلومات ظاہر کرنے کے لیے: براؤزر، صارف ایجنٹ، ملک، وغیرہ۔
2. ریکارڈنگ_ڈیٹا: ریکارڈ شدہ URLs، صفحات، دوروں کا دورانیہ
3. urls: انتہائی بڑے URLs کی نقل تیار کرنے سے بچنے کے لیے، ہم انہیں ایک علیحدہ جدول میں محفوظ کرتے ہیں۔

یہ بھی نوٹ کریں کہ ہماری تمام میزیں پہلے سے ہی تقسیم شدہ ہیں۔ account_id. اس طرح، ایسی صورت حال جہاں ایک خاص طور پر بڑا اکاؤنٹ دوسروں کے لیے مسائل کا باعث بنتا ہے اس کو خارج کر دیا جاتا ہے۔

سراگ تلاش کر رہے ہیں۔

قریب سے معائنہ کرنے پر، ہم دیکھتے ہیں کہ کسی خاص درخواست میں کچھ غلط ہے۔ اس لائن کو قریب سے دیکھنے کے قابل ہے:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

پہلا خیال یہ تھا کہ شاید اس لیے ILIKE ان تمام لمبے URLs پر (ہمارے پاس 1,4 ملین سے زیادہ ہیں۔ انوکھا اس اکاؤنٹ کے لیے جمع کردہ URLs) کی کارکردگی متاثر ہو سکتی ہے۔

لیکن نہیں، یہ بات نہیں ہے!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

ٹیمپلیٹ تلاش کی درخواست میں صرف 5 سیکنڈ لگتے ہیں۔ ایک ملین منفرد URLs میں پیٹرن کی تلاش واضح طور پر کوئی مسئلہ نہیں ہے۔

فہرست میں اگلے مشتبہ افراد کئی ہیں۔ JOIN. شاید ان کا کثرت استعمال سست روی کا سبب بن گیا ہے؟ عام طور پر JOINکارکردگی کے مسائل کے لیے سب سے واضح امیدوار ہیں، لیکن مجھے یقین نہیں تھا کہ ہمارا معاملہ عام ہے۔

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

اور یہ بھی ہمارا معاملہ نہیں تھا۔ JOINکافی تیز نکلا ہے۔

مشتبہ افراد کے دائرے کو کم کرنا

میں کسی بھی ممکنہ کارکردگی میں بہتری حاصل کرنے کے لیے استفسار کو تبدیل کرنے کے لیے تیار تھا۔ میری ٹیم اور میں نے 2 اہم خیالات تیار کیے:

• ذیلی استفسار URL کے لیے EXISTS استعمال کریں۔: ہم دوبارہ چیک کرنا چاہتے تھے کہ آیا یو آر ایل کے ذیلی استفسار میں کوئی مسئلہ ہے یا نہیں۔ اس کو حاصل کرنے کا ایک طریقہ صرف استعمال کرنا ہے۔ EXISTS. EXISTS کر سکتے ہیں کارکردگی کو بہت بہتر بناتا ہے کیونکہ یہ فوری طور پر ختم ہو جاتا ہے جیسے ہی اسے حالت سے مماثل واحد تار ملتا ہے۔

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

ہاں. سبکیوری جب لپیٹ دی جاتی ہے۔ EXISTS، ہر چیز کو انتہائی تیز بنا دیتا ہے۔ اگلا منطقی سوال یہ ہے کہ درخواست کے ساتھ کیوں؟ JOIN-ami اور subquery خود انفرادی طور پر تیز ہیں، لیکن ایک ساتھ بہت سست ہیں؟

• ذیلی استفسار کو CTE میں منتقل کرنا : اگر استفسار اپنے طور پر تیز ہے، تو ہم پہلے تیز رفتار نتیجہ کا حساب لگا سکتے ہیں اور پھر اسے مرکزی استفسار پر فراہم کر سکتے ہیں۔

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

لیکن یہ اب بھی بہت سست تھا۔

مجرم کی تلاش

اس سارے عرصے میں، ایک چھوٹی سی چیز میری آنکھوں کے سامنے چمکتی تھی، جسے میں مسلسل ایک طرف صاف کرتا رہا۔ لیکن چونکہ اور کچھ نہیں بچا تھا اس لیے میں نے اسے بھی دیکھنے کا فیصلہ کیا۔ میں بات کر رہا ہوں۔ && آپریٹر الوداع EXISTS صرف بہتر کارکردگی && سست استفسار کے تمام ورژنز میں واحد باقی ماندہ عام فیکٹر تھا۔

کی طرف دیکھ دستاویزات، ہم دیکھتے ہیں۔ && استعمال کیا جاتا ہے جب آپ کو دو صفوں کے درمیان عام عناصر تلاش کرنے کی ضرورت ہوتی ہے۔

اصل درخواست میں یہ ہے:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

جس کا مطلب ہے کہ ہم اپنے URLs پر پیٹرن کی تلاش کرتے ہیں، پھر مشترکہ پوسٹس کے ساتھ تمام URLs کے ساتھ چوراہا تلاش کرتے ہیں۔ یہ تھوڑا سا الجھا ہوا ہے کیونکہ یہاں "urls" تمام URLs پر مشتمل ٹیبل کا حوالہ نہیں دیتا ہے، بلکہ ٹیبل میں موجود "urls" کالم کا حوالہ دیتا ہے۔ recording_data.

کے حوالے سے بڑھتے ہوئے شکوک کے ساتھ &&، میں نے تیار کردہ استفسار پلان میں ان کے لیے تصدیق تلاش کرنے کی کوشش کی۔ EXPLAIN ANALYZE (میرے پاس پہلے سے ہی ایک منصوبہ محفوظ تھا، لیکن میں عام طور پر استفسار کے منصوبہ سازوں کی دھندلاپن کو سمجھنے کی کوشش کرنے کے بجائے ایس کیو ایل میں تجربہ کرنے میں زیادہ آرام دہ ہوں)۔

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

صرف سے فلٹرز کی کئی لائنیں تھیں۔ &&. جس کا مطلب تھا کہ یہ آپریشن نہ صرف مہنگا تھا بلکہ کئی بار کیا گیا۔

میں نے حالت کو الگ تھلگ کرکے اس کا تجربہ کیا۔

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

یہ استفسار سست تھا۔ کیونکہ JOIN-s تیز ہیں اور ذیلی سوالات تیز ہیں، صرف ایک چیز رہ گئی تھی۔ && آپریٹر

یہ صرف ایک اہم آپریشن ہے۔ پیٹرن کو تلاش کرنے کے لیے ہمیں ہمیشہ URLs کی پوری بنیادی جدول کو تلاش کرنے کی ضرورت ہوتی ہے، اور ہمیں ہمیشہ چوراہوں کو تلاش کرنے کی ضرورت ہوتی ہے۔ ہم براہ راست URL ریکارڈز کے ذریعے تلاش نہیں کر سکتے، کیونکہ یہ صرف IDs کا حوالہ دیتے ہیں۔ urls.

حل کے راستے پر

&& سست کیونکہ دونوں سیٹ بہت بڑے ہیں۔ اگر میں تبدیل کروں تو آپریشن نسبتاً تیز ہو جائے گا۔ urls پر { "http://google.com/", "http://wingify.com/" }.

میں نے بغیر استعمال کیے پوسٹگریس میں سیٹ انٹرسیکشن کرنے کا طریقہ تلاش کرنا شروع کیا۔ &&، لیکن زیادہ کامیابی کے بغیر۔

آخر میں، ہم نے مسئلہ کو تنہائی میں حل کرنے کا فیصلہ کیا: مجھے سب کچھ دے دو urls وہ لائنیں جن کے لیے URL پیٹرن سے مماثل ہے۔ اضافی شرائط کے بغیر یہ ہوگا - 

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

اس کے بجائے JOIN نحو میں نے ابھی ایک ذیلی استفسار استعمال کیا اور توسیع کی۔ recording_data.urls array تاکہ آپ اس شرط کو براہ راست لاگو کر سکیں WHERE.

یہاں سب سے اہم بات یہ ہے۔ && یہ چیک کرنے کے لیے استعمال کیا جاتا ہے کہ آیا دیے گئے اندراج میں مماثل URL شامل ہے۔ اگر آپ تھوڑا سا جھانکتے ہیں، تو آپ دیکھ سکتے ہیں کہ یہ آپریشن ایک صف کے عناصر (یا میز کی قطاروں) کے ذریعے حرکت کرتا ہے اور شرط (میچ) کے پورا ہونے پر رک جاتا ہے۔ کیا آپ کو کچھ یاد نہیں آتا؟ ہاں، EXISTS.

جب سے recording_data.urls ذیلی سوال کے سیاق و سباق سے باہر سے حوالہ دیا جا سکتا ہے، جب ایسا ہوتا ہے تو ہم اپنے پرانے دوست پر واپس آ سکتے ہیں۔ EXISTS اور ذیلی استفسار کو اس کے ساتھ لپیٹ دیں۔

ہر چیز کو ایک ساتھ رکھتے ہوئے، ہمیں حتمی آپٹمائزڈ استفسار ملتا ہے:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

اور آخری لیڈ ٹائم Time: 1898.717 ms منانے کا وقت؟!؟

اتنا تیز نہیں! سب سے پہلے آپ کو درستگی کی جانچ کرنے کی ضرورت ہے. میں انتہائی مشکوک تھا۔ EXISTS اصلاح کیونکہ یہ منطق کو پہلے ختم کرنے کے لیے تبدیل کرتی ہے۔ ہمیں اس بات کا یقین کرنے کی ضرورت ہے کہ ہم نے درخواست میں کوئی غیر واضح غلطی شامل نہیں کی ہے۔

ایک سادہ امتحان چلانا تھا۔ count(*) مختلف ڈیٹا سیٹس کی ایک بڑی تعداد کے لیے سست اور تیز دونوں سوالات پر۔ پھر، ڈیٹا کے ایک چھوٹے ذیلی سیٹ کے لیے، میں نے دستی طور پر تصدیق کی کہ تمام نتائج درست تھے۔

تمام ٹیسٹوں نے مسلسل مثبت نتائج دیے۔ ہم نے سب کچھ ٹھیک کر دیا!

سبق سیکھا

اس کہانی سے سیکھنے کے لیے بہت سے سبق ہیں:

1. استفسار کے منصوبے پوری کہانی نہیں بتاتے، لیکن وہ سراگ فراہم کر سکتے ہیں۔
2. اصل ملزمان ہمیشہ اصل مجرم نہیں ہوتے
3. رکاوٹوں کو الگ کرنے کے لیے سست سوالات کو توڑا جا سکتا ہے۔
4. تمام اصلاح فطرت میں تخفیف آمیز نہیں ہیں۔
5. استعمال کریں EXISTجہاں ممکن ہو، پیداواری صلاحیت میں ڈرامائی اضافہ کا باعث بن سکتا ہے۔

آؤٹ پٹ

ہم ~24 منٹ کے استفسار کے وقت سے 2 سیکنڈ تک چلے گئے - کارکردگی میں کافی اضافہ! اگرچہ یہ مضمون بڑا سامنے آیا، لیکن ہم نے جتنے بھی تجربات کیے وہ ایک دن میں ہوئے، اور یہ اندازہ لگایا گیا کہ ان کی اصلاح اور جانچ میں 1,5 سے 2 گھنٹے لگے۔

SQL ایک شاندار زبان ہے اگر آپ اس سے خوفزدہ نہیں ہیں، لیکن اسے سیکھنے اور استعمال کرنے کی کوشش کریں۔ ایس کیو ایل کے سوالات کو کیسے انجام دیا جاتا ہے، ڈیٹا بیس کس طرح استفسار کے منصوبے تیار کرتا ہے، اشاریہ جات کیسے کام کرتے ہیں، اور جس ڈیٹا کے ساتھ آپ کام کر رہے ہیں، اس کے بارے میں اچھی طرح سمجھ کر، آپ سوالات کو بہتر بنانے میں بہت کامیاب ہو سکتے ہیں۔ تاہم، یہ بھی اتنا ہی اہم ہے کہ مختلف طریقوں کو آزمانا جاری رکھیں اور رکاوٹوں کو تلاش کرتے ہوئے آہستہ آہستہ مسئلہ کو ختم کریں۔

اس طرح کے نتائج حاصل کرنے کے بارے میں سب سے اچھی بات قابل توجہ، نظر آنے والی رفتار میں بہتری ہے - جہاں ایک رپورٹ جو پہلے لوڈ نہیں ہوتی تھی اب تقریباً فوری طور پر لوڈ ہو جاتی ہے۔

خصوصی شکریہ میرے ساتھیوں آدتیہ مشرا کے حکم پر, آدتیہ گورو и ورون ملہوترا ذہن سازی کے لیے اور دنکر پنڈیر ہماری آخری درخواست میں ایک اہم خامی تلاش کرنے کے لیے اس سے پہلے کہ ہم اسے الوداع کہہ دیں!

ماخذ: www.habr.com