Hello,
I have a table with 31 million records. So far most queries return within 2-4 seconds but there is one important query that is taking 14 seconds.
There is a field called FEDERAL_WASTE_CODES and 75% of the time it contains <= 3 waste codes (D001, F002, F003) and 95% of the time it contains <= 10 waste codes (D004, D005, D006, D007, D008, D009, D010, D011).
However, there are times it contains 40+ Waste Codes (see end of post). When I filter for a specific waste code (LIKE '%K061%') the query becomes too slow. I did not index this field.
What are your recommendations? Thank you!
This query takes ~14 seconds when done in C#
D001, D004, D005, D006, D007, D008, D009, D010, D011, D012, D013, D014, D015, D016, D017, D018, D019, D020, D021, D022, D023, D024, D025, D026, D027, D028, D029, D030, D031, D032, D033, D034, D035, D036, D037, D038, D039, D040, D041, D042, D043, F007, F008, F009, F010, F011, F012, F019, F024, F025, F037, F038, F039, K001, K002, K003, K004, K005, K006, K007, K008, K009, K010, K011, K013, K014, K015, K016, K017, K018, K019, K020, K021, K022, K023, K024, K028, K029, K030, K031, K032, K033, K034, K035, K036, K037, K038, K040, K041, K042, K043, K044, K045, K046, K047, K048, K049, K050, K051, K052, K060, K061, K062, K069, K071, K073, K083, K084, K085, K086, K087, K088, K093, K094, K095, K096, K097, K098, K099, K100, K101, K102, K103, K104, K105, K106, K111, K115, K117, K118, K131, K132, K136, K141, K142, K143, K144, K145, K147, K148, K149, K150, K151, K156, K157, K158, K159, K161, P004, P006, P009, P010, P011, P012, P013, P020, P021, P022, P024, P029, P030, P031, P033, P034, P036, P037, P038, P039, P047, P048, P050, P051, P056, P059, P060, P063, P065, P068, P071, P073, P074, P077, P081, P082, P089, P092, P094, P096, P097, P098, P099, P101, P103, P104, P105, P106, P110, P112, P113, P114, P115, P119, P120, P121, P122, P123, P127, P128, P185, P188, P189, P190, P191, P192, P194, P196, P197, P198, P199, P201, P202, P203, P204, P205, U005, U009, U012, U018, U019, U022, U023, U024, U025, U027, U028, U029, U030, U031, U032, U036, U037, U039, U043, U044, U045, U047, U048, U050, U051, U052, U057, U060, U061, U063, U066, U067, U068, U069, U070, U071, U072, U075, U076, U077, U078, U079, U080, U081, U082, U083, U084, U086, U088, U096, U098, U099, U101, U102, U103, U105, U106, U107, U108, U109, U111, U112, U115, U117, U118, U120, U121, U124, U127, U128, U129, U130, U131, U133, U134, U135, U136, U137, U138, U140, U141, U142, U144, U145, U146, U151, U152, U154, U155, U157, U158, U159, U160, U161, U162, U165, U169, U170, U172, U174, U179, U180, U181, U183, U184, U185, U187, U188, U189, U190, U192, U196, U203, U204, U205, U207, U208, U209, U210, U211, U214, U215, U216, U217, U220, U225, U226, U227, U228, U235, U239, U240, U243, U246, U247, U249, U271, U278, U279, U280, U364, U367, U372, U373, U387, U389, U394, U395, U404, U409, U410, U411
I have a table with 31 million records. So far most queries return within 2-4 seconds but there is one important query that is taking 14 seconds.
There is a field called FEDERAL_WASTE_CODES and 75% of the time it contains <= 3 waste codes (D001, F002, F003) and 95% of the time it contains <= 10 waste codes (D004, D005, D006, D007, D008, D009, D010, D011).
However, there are times it contains 40+ Waste Codes (see end of post). When I filter for a specific waste code (LIKE '%K061%') the query becomes too slow. I did not index this field.
What are your recommendations? Thank you!
This query takes ~14 seconds when done in C#
Code:
return await dbContext.ManifestLines
.Where(x => x.SHIPPED_DATE.Year == 2024
&& x.GENERATOR_LOCATION_STATE == "NY"
&& x.FEDERAL_WASTE_CODES != null
&& x.FEDERAL_WASTE_CODES.Contains("K061"))
.ToListAsync();
D001, D004, D005, D006, D007, D008, D009, D010, D011, D012, D013, D014, D015, D016, D017, D018, D019, D020, D021, D022, D023, D024, D025, D026, D027, D028, D029, D030, D031, D032, D033, D034, D035, D036, D037, D038, D039, D040, D041, D042, D043, F007, F008, F009, F010, F011, F012, F019, F024, F025, F037, F038, F039, K001, K002, K003, K004, K005, K006, K007, K008, K009, K010, K011, K013, K014, K015, K016, K017, K018, K019, K020, K021, K022, K023, K024, K028, K029, K030, K031, K032, K033, K034, K035, K036, K037, K038, K040, K041, K042, K043, K044, K045, K046, K047, K048, K049, K050, K051, K052, K060, K061, K062, K069, K071, K073, K083, K084, K085, K086, K087, K088, K093, K094, K095, K096, K097, K098, K099, K100, K101, K102, K103, K104, K105, K106, K111, K115, K117, K118, K131, K132, K136, K141, K142, K143, K144, K145, K147, K148, K149, K150, K151, K156, K157, K158, K159, K161, P004, P006, P009, P010, P011, P012, P013, P020, P021, P022, P024, P029, P030, P031, P033, P034, P036, P037, P038, P039, P047, P048, P050, P051, P056, P059, P060, P063, P065, P068, P071, P073, P074, P077, P081, P082, P089, P092, P094, P096, P097, P098, P099, P101, P103, P104, P105, P106, P110, P112, P113, P114, P115, P119, P120, P121, P122, P123, P127, P128, P185, P188, P189, P190, P191, P192, P194, P196, P197, P198, P199, P201, P202, P203, P204, P205, U005, U009, U012, U018, U019, U022, U023, U024, U025, U027, U028, U029, U030, U031, U032, U036, U037, U039, U043, U044, U045, U047, U048, U050, U051, U052, U057, U060, U061, U063, U066, U067, U068, U069, U070, U071, U072, U075, U076, U077, U078, U079, U080, U081, U082, U083, U084, U086, U088, U096, U098, U099, U101, U102, U103, U105, U106, U107, U108, U109, U111, U112, U115, U117, U118, U120, U121, U124, U127, U128, U129, U130, U131, U133, U134, U135, U136, U137, U138, U140, U141, U142, U144, U145, U146, U151, U152, U154, U155, U157, U158, U159, U160, U161, U162, U165, U169, U170, U172, U174, U179, U180, U181, U183, U184, U185, U187, U188, U189, U190, U192, U196, U203, U204, U205, U207, U208, U209, U210, U211, U214, U215, U216, U217, U220, U225, U226, U227, U228, U235, U239, U240, U243, U246, U247, U249, U271, U278, U279, U280, U364, U367, U372, U373, U387, U389, U394, U395, U404, U409, U410, U411