locked
Partition elimination on a join, is that possible RRS feed

  • Question

  • We have a large table with 400mil rows and 3265 partitions Partitioned on column on PartCol (lets say) and it's clustered unique index is PartCol, ColB, ColC. We got a table with 200K records that has random selected exactly 1000 partitions (1000 distinct PartCol) and total 200K records for those 1000 partitions. The clustered index on the not partitioned table is also PartCol, ColB, ColC and that's the only 3 columns the table has. Is there anything I can do to make sure the query will only touch the 1000 partitions coming from the keys table, today when I join them all 3265 partitions are scanned, the key table has a freshly created clustered index and good statistics.

    Should I try to create the key table on the same partitioning function function maybe this way elimination work maybe?

    Thank you

    


    Gokhan Varol

    • Moved by Tom Phillips Thursday, June 21, 2012 1:38 PM Query optimization question (From:SQL Server Database Engine)
    Saturday, June 16, 2012 1:27 AM

Answers

  • This query works every time, eliminates to the right partition and uses clustered index seek on the large partitioned table and on a warm sql server is very fast and after detaching and attaching the database and so forth cleaning the cache for the database is slower( couple seconds vs 5-8 minutes with physical io) but still lots quicker than any other method. If you only use the cross apply without the top clause then query optimizer can (and will) convert it to a simpler join and do clustered index scan instead, performs bad, by having the top clause it uses the clustered index seek.

    It would be better to use a FORCESEEK hint here, rather than CROSS APPLY and TOP.  Query hints (and the like) are the preferred mechanisms to help the optimizer find a good plan when your data does not fit its model.  Using the APPLY & TOP syntax may result in the execution plan you want today, but it is not guaranteed to do so.  Remember what happened to TOP (100) PERCENT.

    The core issue is that the optimizer's costing model does not fit your data and hardware in this specific case.  It estimates that scanning the whole table (taking advantage of read-ahead and sequential I/O) will be cheaper than performing 200,000 seeks.  It turns out that you see much better performance with the seeks than the scan, so the correct override is to force the seek.  Naturally, 200,000 seeks will not make as efficient use of read-ahead and sequential I/O as the scan (even accounting for nested loops pre-fetching), but you seem willing to make that trade-off.

    On the issue of partition elimination, what you are seeing with the seeks is dynamic partition elimination.  The RangePartitionNew function inside the seek calculates the correct partition id for every outer row at runtime.  This mechanism is generally available only nested loops joins (not hash or merge), since the correct partition for the seek needs to be calculated once per outer row.

    More generally, it seems that you are looking to use partitioning for performance reasons.  This is generally an error, in my experience.  There are all sorts of little issues and complications (that you are only beginning to encounter) which mean that partitioning is, generally speaking, best not used as a performance-enhancing feature, rather as a manageability one.  It may seem natural and 'obvious' that eliminating whole partitions would benefit performance, but this is generally not true, given correct design and optimal non-partitioned indexing in particular.

    Going beyond the 1,000 partition limit using the (somewhat shaky) extensions added in a Service Pack seems a high-risk strategy to me.  If I were forced to consider this level of partitioning, I would certainly want to be doing it on SQL Server 2012.  It's also worth noting that you are a full Service Pack behind current for SQL Server 2008, which might help explain the assertions (perhaps).  I will stress again though that I think (this level of) partitioning is an error, given what you have said about your needs in this and previous threads (unless, perhaps, partitioning on CntyCd gives you significant manageability benefits).

    I would be very tempted to stop now, and re-think the whole design rather than continuing to experiment like this.  It is certainly possible (with great care and expertise) to craft a high-performance system of this sort in T-SQL and SQLCLR, and it is equally possible to do it using SSIS.  The Data Loading Performance Guide has some good information and considerations, with and without partitioning.  There are a large number of other resources out there too, including experts who have real-world experience of doing this sort of thing on even larger systems than yours.

    As constructive feedback for your next question, try to ask your question more simply and precisely next time.  Including sample code that people without access to your databases can use directly will also help a great deal.  Providing a statistics-only copy of the tables in question is a great option, and much easier to work with for us.  There are a number of online facilities to make large scripts (and *.sqlplan files rather than images!) available (e.g. pastebin), which can make posts here more concise as well.

    Finally, here's a demo based loosely on the sort of data structures you appear to have, showing the parallel nested-loops plan with dynamic partition elimination, and the optional FORCESEEK hint:

    USE Sandpit;
    GO
    CREATE PARTITION FUNCTION PF (integer)
    AS RANGE RIGHT
    FOR VALUES
        (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1389,1390,1391,1392,1393,1394,1395,1396,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1527,1528,1529,1530,1531,1532,1533,1534,1535,1536,1537,1538,1539,1540,1541,1542,1543,1544,1545,1546,1547,1548,1549,1550,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,1568,1569,1570,1571,1572,1573,1574,1575,1576,1577,1578,1579,1580,1581,1582,1583,1584,1585,1586,1587,1588,1589,1590,1591,1592,1593,1594,1595,1596,1597,1598,1599,1600,1601,1602,1603,1604,1605,1606,1607,1608,1609,1610,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641,1642,1643,1644,1645,1646,1647,1648,1649,1650,1651,1652,1653,1654,1655,1656,1657,1658,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,1674,1675,1676,1677,1678,1679,1680,1681,1682,1683,1684,1685,1686,1687,1688,1689,1690,1691,1692,1693,1694,1695,1696,1697,1698,1699,1700,1701,1702,1703,1704,1705,1706,1707,1708,1709,1710,1711,1712,1713,1714,1715,1716,1717,1718,1719,1720,1721,1722,1723,1724,1725,1726,1727,1728,1729,1730,1731,1732,1733,1734,1735,1736,1737,1738,1739,1740,1741,1742,1743,1744,1745,1746,1747,1748,1749,1750,1751,1752,1753,1754,1755,1756,1757,1758,1759,1760,1761,1762,1763,1764,1765,1766,1767,1768,1769,1770,1771,1772,1773,1774,1775,1776,1777,1778,1779,1780,1781,1782,1783,1784,1785,1786,1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798,1799,1800,1801,1802,1803,1804,1805,1806,1807,1808,1809,1810,1811,1812,1813,1814,1815,1816,1817,1818,1819,1820,1821,1822,1823,1824,1825,1826,1827,1828,1829,1830,1831,1832,1833,1834,1835,1836,1837,1838,1839,1840,1841,1842,1843,1844,1845,1846,1847,1848,1849,1850,1851,1852,1853,1854,1855,1856,1857,1858,1859,1860,1861,1862,1863,1864,1865,1866,1867,1868,1869,1870,1871,1872,1873,1874,1875,1876,1877,1878,1879,1880,1881,1882,1883,1884,1885,1886,1887,1888,1889,1890,1891,1892,1893,1894,1895,1896,1897,1898,1899,1900,1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911,1912,1913,1914,1915,1916,1917,1918,1919,1920,1921,1922,1923,1924,1925,1926,1927,1928,1929,1930,1931,1932,1933,1934,1935,1936,1937,1938,1939,1940,1941,1942,1943,1944,1945,1946,1947,1948,1949,1950,1951,1952,1953,1954,1955,1956,1957,1958,1959,1960,1961,1962,1963,1964,1965,1966,1967,1968,1969,1970,1971,1972,1973,1974,1975,1976,1977,1978,1979,1980,1981,1982,1983,1984,1985,1986,1987,1988,1989,1990,1991,1992,1993,1994,1995,1996,1997,1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2043,2044,2045,2046,2047,2048,2049,2050,2051,2052,2053,2054,2055,2056,2057,2058,2059,2060,2061,2062,2063,2064,2065,2066,2067,2068,2069,2070,2071,2072,2073,2074,2075,2076,2077,2078,2079,2080,2081,2082,2083,2084,2085,2086,2087,2088,2089,2090,2091,2092,2093,2094,2095,2096,2097,2098,2099,2100,2101,2102,2103,2104,2105,2106,2107,2108,2109,2110,2111,2112,2113,2114,2115,2116,2117,2118,2119,2120,2121,2122,2123,2124,2125,2126,2127,2128,2129,2130,2131,2132,2133,2134,2135,2136,2137,2138,2139,2140,2141,2142,2143,2144,2145,2146,2147,2148,2149,2150,2151,2152,2153,2154,2155,2156,2157,2158,2159,2160,2161,2162,2163,2164,2165,2166,2167,2168,2169,2170,2171,2172,2173,2174,2175,2176,2177,2178,2179,2180,2181,2182,2183,2184,2185,2186,2187,2188,2189,2190,2191,2192,2193,2194,2195,2196,2197,2198,2199,2200,2201,2202,2203,2204,2205,2206,2207,2208,2209,2210,2211,2212,2213,2214,2215,2216,2217,2218,2219,2220,2221,2222,2223,2224,2225,2226,2227,2228,2229,2230,2231,2232,2233,2234,2235,2236,2237,2238,2239,2240,2241,2242,2243,2244,2245,2246,2247,2248,2249,2250,2251,2252,2253,2254,2255,2256,2257,2258,2259,2260,2261,2262,2263,2264,2265,2266,2267,2268,2269,2270,2271,2272,2273,2274,2275,2276,2277,2278,2279,2280,2281,2282,2283,2284,2285,2286,2287,2288,2289,2290,2291,2292,2293,2294,2295,2296,2297,2298,2299,2300,2301,2302,2303,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2326,2327,2328,2329,2330,2331,2332,2333,2334,2335,2336,2337,2338,2339,2340,2341,2342,2343,2344,2345,2346,2347,2348,2349,2350,2351,2352,2353,2354,2355,2356,2357,2358,2359,2360,2361,2362,2363,2364,2365,2366,2367,2368,2369,2370,2371,2372,2373,2374,2375,2376,2377,2378,2379,2380,2381,2382,2383,2384,2385,2386,2387,2388,2389,2390,2391,2392,2393,2394,2395,2396,2397,2398,2399,2400,2401,2402,2403,2404,2405,2406,2407,2408,2409,2410,2411,2412,2413,2414,2415,2416,2417,2418,2419,2420,2421,2422,2423,2424,2425,2426,2427,2428,2429,2430,2431,2432,2433,2434,2435,2436,2437,2438,2439,2440,2441,2442,2443,2444,2445,2446,2447,2448,2449,2450,2451,2452,2453,2454,2455,2456,2457,2458,2459,2460,2461,2462,2463,2464,2465,2466,2467,2468,2469,2470,2471,2472,2473,2474,2475,2476,2477,2478,2479,2480,2481,2482,2483,2484,2485,2486,2487,2488,2489,2490,2491,2492,2493,2494,2495,2496,2497,2498,2499,2500,2501,2502,2503,2504,2505,2506,2507,2508,2509,2510,2511,2512,2513,2514,2515,2516,2517,2518,2519,2520,2521,2522,2523,2524,2525,2526,2527,2528,2529,2530,2531,2532,2533,2534,2535,2536,2537,2538,2539,2540,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2551,2552,2553,2554,2555,2556,2557,2558,2559,2560,2561,2562,2563,2564,2565,2566,2567,2568,2569,2570,2571,2572,2573,2574,2575,2576,2577,2578,2579,2580,2581,2582,2583,2584,2585,2586,2587,2588,2589,2590,2591,2592,2593,2594,2595,2596,2597,2598,2599,2600,2601,2602,2603,2604,2605,2606,2607,2608,2609,2610,2611,2612,2613,2614,2615,2616,2617,2618,2619,2620,2621,2622,2623,2624,2625,2626,2627,2628,2629,2630,2631,2632,2633,2634,2635,2636,2637,2638,2639,2640,2641,2642,2643,2644,2645,2646,2647,2648,2649,2650,2651,2652,2653,2654,2655,2656,2657,2658,2659,2660,2661,2662,2663,2664,2665,2666,2667,2668,2669,2670,2671,2672,2673,2674,2675,2676,2677,2678,2679,2680,2681,2682,2683,2684,2685,2686,2687,2688,2689,2690,2691,2692,2693,2694,2695,2696,2697,2698,2699,2700,2701,2702,2703,2704,2705,2706,2707,2708,2709,2710,2711,2712,2713,2714,2715,2716,2717,2718,2719,2720,2721,2722,2723,2724,2725,2726,2727,2728,2729,2730,2731,2732,2733,2734,2735,2736,2737,2738,2739,2740,2741,2742,2743,2744,2745,2746,2747,2748,2749,2750,2751,2752,2753,2754,2755,2756,2757,2758,2759,2760,2761,2762,2763,2764,2765,2766,2767,2768,2769,2770,2771,2772,2773,2774,2775,2776,2777,2778,2779,2780,2781,2782,2783,2784,2785,2786,2787,2788,2789,2790,2791,2792,2793,2794,2795,2796,2797,2798,2799,2800,2801,2802,2803,2804,2805,2806,2807,2808,2809,2810,2811,2812,2813,2814,2815,2816,2817,2818,2819,2820,2821,2822,2823,2824,2825,2826,2827,2828,2829,2830,2831,2832,2833,2834,2835,2836,2837,2838,2839,2840,2841,2842,2843,2844,2845,2846,2847,2848,2849,2850,2851,2852,2853,2854,2855,2856,2857,2858,2859,2860,2861,2862,2863,2864,2865,2866,2867,2868,2869,2870,2871,2872,2873,2874,2875,2876,2877,2878,2879,2880,2881,2882,2883,2884,2885,2886,2887,2888,2889,2890,2891,2892,2893,2894,2895,2896,2897,2898,2899,2900,2901,2902,2903,2904,2905,2906,2907,2908,2909,2910,2911,2912,2913,2914,2915,2916,2917,2918,2919,2920,2921,2922,2923,2924,2925,2926,2927,2928,2929,2930,2931,2932,2933,2934,2935,2936,2937,2938,2939,2940,2941,2942,2943,2944,2945,2946,2947,2948,2949,2950,2951,2952,2953,2954,2955,2956,2957,2958,2959,2960,2961,2962,2963,2964,2965,2966,2967,2968,2969,2970,2971,2972,2973,2974,2975,2976,2977,2978,2979,2980,2981,2982,2983,2984,2985,2986,2987,2988,2989,2990,2991,2992,2993,2994,2995,2996,2997,2998,2999,3000,3001,3002,3003,3004,3005,3006,3007,3008,3009,3010,3011,3012,3013,3014,3015,3016,3017,3018,3019,3020,3021,3022,3023,3024,3025,3026,3027,3028,3029,3030,3031,3032,3033,3034,3035,3036,3037,3038,3039,3040,3041,3042,3043,3044,3045,3046,3047,3048,3049,3050,3051,3052,3053,3054,3055,3056,3057,3058,3059,3060,3061,3062,3063,3064,3065,3066,3067,3068,3069,3070,3071,3072,3073,3074,3075,3076,3077,3078,3079,3080,3081,3082,3083,3084,3085,3086,3087,3088,3089,3090,3091,3092,3093,3094,3095,3096,3097,3098,3099,3100,3101,3102,3103,3104,3105,3106,3107,3108,3109,3110,3111,3112,3113,3114,3115,3116,3117,3118,3119,3120,3121,3122,3123,3124,3125,3126,3127,3128,3129,3130,3131,3132,3133,3134,3135,3136,3137,3138,3139,3140,3141,3142,3143,3144,3145,3146,3147,3148,3149,3150,3151,3152,3153,3154,3155,3156,3157,3158,3159,3160,3161,3162,3163,3164,3165,3166,3167,3168,3169,3170,3171,3172,3173,3174,3175,3176,3177,3178,3179,3180,3181,3182,3183,3184,3185,3186,3187,3188,3189,3190,3191,3192,3193,3194,3195,3196,3197,3198,3199,3200,3201,3202,3203,3204,3205,3206,3207,3208,3209,3210,3211,3212,3213,3214,3215,3216,3217,3218,3219,3220,3221,3222,3223,3224,3225,3226,3227,3228,3229,3230,3231,3232,3233,3234,3235,3236,3237,3238,3239,3240,3241,3242,3243,3244,3245,3246,3247,3248,3249,3250,3251,3252,3253,3254,3255,3256,3257,3258,3259,3260,3261,3262,3263,3264,3265);
    GO
    CREATE PARTITION SCHEME PS
    AS PARTITION PF
    ALL TO ([PRIMARY])
    GO
    CREATE TABLE dbo.Test
    (
        PartCol integer NOT NULL,
        ColB    integer NOT NULL,
        ColC    integer NOT NULL,
        Padding char(120) NOT NULL
    
        PRIMARY KEY CLUSTERED (PartCol, ColB, ColC)
        ON PS(PartCol)
    );
    GO
    CREATE TABLE dbo.PKeys
    (
        PartCol integer NOT NULL,
        ColB    integer NOT NULL,
        ColC    integer NOT NULL,
        
        PRIMARY KEY CLUSTERED (PartCol, ColB, ColC)
        ON [PRIMARY]
    );
    GO
    -- Pretend there are 4,000,000 rows
    UPDATE STATISTICS dbo.Test WITH ROWCOUNT = 400000000, PAGECOUNT = 4000000;
    GO
    -- Pretend there are 200,000 rows
    UPDATE STATISTICS dbo.PKeys WITH ROWCOUNT = 200000, PAGECOUNT = 200;
    GO
    -- Parallel nested loops with dynamic elimination
    -- Uncomment the FORCESEEK hint only if required
    -- on your system.
    SELECT t.*
    FROM dbo.PKeys AS pk
    JOIN dbo.Test AS t /*WITH (FORCESEEK)*/ ON
        t.PartCol = pk.PartCol
        AND t.ColB = pk.ColB
        AND t.ColC = pk.ColC;
    GO
    -- Clean up
    DROP TABLE dbo.Test, dbo.PKeys;
    DROP PARTITION SCHEME PS;
    DROP PARTITION FUNCTION PF;
    
    Query plan:

    Parallel Nested Loops

    • Marked as answer by Iric Wen Tuesday, June 26, 2012 1:43 AM
    Wednesday, June 20, 2012 6:23 PM

All replies

  • I would like to give a preference to go with partitioning table on the same PK value (if it is clustered index)  and in other words to make the partitioning  key is the same PK used for joins to assure aligned partitioning using collation form which might help significantly  queries joining this table with other tables on the same PK since  query analyzer is going to just align its search scope according to  the portioning ranges that increase IO parallel processing   

    Moreover , you could get better performance reads if applied storage aligned partitioning  by partitioning all non clustered indexes on the same partitioning schema  that increase both IO +CPU parallel processing    

    By that time

    Let me know if any further help needed


    Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

    Saturday, June 16, 2012 9:40 AM
  • Hi, Gokhan!

    I love problems like this!  Makes me laugh, actually.  Here you are, you've done everything SQL Server gives you, to break your data into partitions that COULD be efficiently searched, but the compiler/optimizer just does not seem to want to do what should (?) be "obvious"!

    The problem is that you have the names of the 1,000 hot partitions not in the SQL statement, but in a data table.  I'm not aware of any time that the compiler/optimizer will be that dynamic, looking at *data*, in order to develop an efficient plan.

    It so happened that I worked with a guy who was really the king of generating dynamic SQL, writing TSQL statements that would generate stuff on the fly that I would have done statically.  We were trying to do stuff with a table with over 1,000 columns, so even doing it statically typically involved writing code to generate code!  Anyway, I think you need to do some of that here.

    So, here are three steps.

    The first step is just to satisfy my curiosity, and tell me how long does it take to execute right now, scanning all 3265 partitions?  Because at best we're only (!) going to eliminate 2/3 of them, and scanning 2/3 that have no matches shouldn't take all that long anyway, should it?

    ----------

    The second step is to try to do your query in one step, but letting the compiler see the dynamic data as static, literal values.  Generate the SQL statement like:

    select big.* from bigtable big inner join smalltable small on big.PartCol=small.PartCol and big.ColB=small.ColB and big.ColC=small.ColC and big.PartCol in (1,3,11,13,19,22,23,29,1100,1101,...3260);

    The thing is: give it literal values!

    Will SQL Server be smart enough to do the right thing with this, when it is SO many partitions still?  I dunno.

    ----------

    If the second step works, you can go play golf or something, otherwise you would have to go on and try one more thing, that SHOULD WORK, but is a pain.

    The third step is to generate 1,000 separate SQL statements where you can limit the partition strictly, one at a time:

    select big.* from bigtable big inner join smalltable small on big.PartCol=small.PartCol and big.ColB=small.ColB and big.ColC=small.ColC and big.PartCol =1; 

    select big.* from bigtable big inner join smalltable small on big.PartCol=small.PartCol and big.ColB=small.ColB and big.ColC=small.ColC and big.PartCol =3;

    -- etc

    Now, this will certainly work, but of course will have to compile 1,000 SQL statements instead of just precompiling one into an SP.  Is it really faster to (generate and) compile 1,000 statements than it is to simply, back in step zero, let SQL Server look at 2,265 partitions that don't match?  

    I dunno.  But it's fun stuff to check.

    Good luck, and if you go through this I hope you will share your results!

    Josh





    • Edited by JRStern Saturday, June 16, 2012 3:41 PM
    Saturday, June 16, 2012 3:37 PM
  • Josh

    I created a single column 1000 row table and created a unique clustered index and used it as

    ...

    In (select pka from cols)

    6 out of 8 queries eliminated to 1000 portions. I will play around with the other 2 later when I get home


    Gokhan Varol

    • Proposed as answer by Hetts Wednesday, December 11, 2013 4:46 PM
    • Unproposed as answer by Hetts Wednesday, December 11, 2013 4:46 PM
    Saturday, June 16, 2012 4:40 PM
  • Josh

    If you are interested check this out, seems to work so far. Notice the comment sections within the sql statements

    Thanks

    -- TABLE Temporary.dbo.VTC_SampleData2 HAS 1000 distinct partition key values and 200K records from those 1000 distinct partition key values.
    -- The objects under Trans, tTrans, tTax schemas have between 3100 and 3300 partitions
    -- When a process runs from the full tables directly it takes over 7000 seconds.
    -- The same process ran against a subset of data (not partitioned, freshly indexed, limited columns, limited rows) as below it takes 32 seconds
    -- To create the temporary tables below takes over 45 minutes, realized the partitions were not eliminated in the below queries and 
    -- all 3000+ partitions were scanned against the small key table containing 200K records
    -- After some testing I found out to eliminate the partitions I created a table that has 1000 records with partition key values 
    -- and in inner query joined that to the main tables and then I used the alias from the small key table for the partition key and the rest bring the columns 
    -- from main table and then in the outer query join the data back to 200K key table. This query eliminates partitions by forcing the partition key (CntyCd) to be read
    -- from small key table.
    
    
    IF OBJECT_ID('Temporary.dbo.VTC_Property') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_Property
    IF OBJECT_ID('Temporary.dbo.VTC_Trans_ER') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_Trans_ER
    IF OBJECT_ID('Temporary.dbo.VTC_Trans') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_Trans
    IF OBJECT_ID('Temporary.dbo.VTC_TransLegalPartyName') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_TransLegalPartyName
    IF OBJECT_ID('Temporary.dbo.VTC_TransLegalPartyParsed') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_TransLegalPartyParsed
    IF OBJECT_ID('Temporary.dbo.VTC_TransMortgage') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_TransMortgage
    
    IF OBJECT_ID('Temporary.dbo.VTC_SampleDataCntyCd') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_SampleDataCntyCd
    
    -- This table will hold the partition key values
    SELECT DISTINCT
            CntyCd
    INTO    Temporary.dbo.VTC_SampleDataCntyCd
    FROM    Temporary.dbo.VTC_SampleData2
    
    CREATE UNIQUE CLUSTERED INDEX CntyCd ON Temporary.dbo.VTC_SampleDataCntyCd(CntyCd)
    
    SELECT  a.CntyCd
          , a.BatchDt
          , a.BatchSeq
          , a.AvmValAmt
          , a.BeneficiaryPurchasedInd
          , a.ChronoNbr
          , a.CltvPct
          , a.CurrIEQAmt
          , a.CurrIEQDt
          , a.CurrIEQPct
          , a.DeedCatTyp
          , a.DocTyp
          , a.EditCounter
          , a.EditReqdInd
          , a.EditTimestamp
          , a.InterrelatedInd
          , a.IrisLandUseCd
          , a.LegalMapTypCd
          , a.MultiSplitCd
          , a.NominalInd
          , a.OltvPct
          , a.OrigIeqAmt
          , a.OrigIeqDt
          , a.OrigIeqPct
          , a.OrigRecordingDtDerived
          , a.PartialInterestTransferInd
          , a.PclId
          , a.PclSeqNbr
          , a.PriceCalcInd
          , a.PriceSqFtAmt
          , a.PrimaryCatCd
          , a.PropIndCd
          , a.RealEstateOwnedInd
          , a.RealEstateOwnedSaleInd
          , a.RecordingDt
          , a.RecordTyp
          , a.RejectInd
          , a.ResModelInd
          , a.SaleDt
          , a.SalePosition
          , a.SalePriceAmt
          , a.SecDCRealEstateOwnedSaleInd
          , a.SourceInd
          , a.StndAloneMtgInd
          , a.TaxStampAmt
          , a.TaxStampAmtCd
          , a.TransTyp
          , a.VestingPosition
    INTO    Temporary.dbo.VTC_Trans_ER
    FROM    (SELECT b.CntyCd /* Alias Must be b */
                  , a.BatchDt
                  , a.BatchSeq
                  , a.AvmValAmt
                  , a.BeneficiaryPurchasedInd
                  , a.ChronoNbr
                  , a.CltvPct
                  , a.CurrIEQAmt
                  , a.CurrIEQDt
                  , a.CurrIEQPct
                  , a.DeedCatTyp
                  , a.DocTyp
                  , a.EditCounter
                  , a.EditReqdInd
                  , a.EditTimestamp
                  , a.InterrelatedInd
                  , a.IrisLandUseCd
                  , a.LegalMapTypCd
                  , a.MultiSplitCd
                  , a.NominalInd
                  , a.OltvPct
                  , a.OrigIeqAmt
                  , a.OrigIeqDt
                  , a.OrigIeqPct
                  , a.OrigRecordingDtDerived
                  , a.PartialInterestTransferInd
                  , a.PclId
                  , a.PclSeqNbr
                  , a.PriceCalcInd
                  , a.PriceSqFtAmt
                  , a.PrimaryCatCd
                  , a.PropIndCd
                  , a.RealEstateOwnedInd
                  , a.RealEstateOwnedSaleInd
                  , a.RecordingDt
                  , a.RecordTyp
                  , a.RejectInd
                  , a.ResModelInd
                  , a.SaleDt
                  , a.SalePosition
                  , a.SalePriceAmt
                  , a.SecDCRealEstateOwnedSaleInd
                  , a.SourceInd
                  , a.StndAloneMtgInd
                  , a.TaxStampAmt
                  , a.TaxStampAmtCd
                  , a.TransTyp
                  , a.VestingPosition
             FROM   Trans.vTrans a
             INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON a.CntyCd = b.CntyCd) a
    INNER JOIN Temporary.dbo.VTC_SampleData2 b ON a.CntyCd = b.CntyCd
                                                  AND a.BatchDt = b.BatchDt
                                                  AND a.BatchSeq = b.BatchSeq
            
    CREATE UNIQUE CLUSTERED INDEX VTC_Trans_ER ON Temporary.dbo.VTC_Trans_ER (CntyCd, BatchDt, BatchSeq)
    -- DROP INDEX VTC_Trans_PCL ON Temporary.dbo.VTC_Trans_ER 
    CREATE NONCLUSTERED INDEX VTC_Trans_PCL ON Temporary.dbo.VTC_Trans_ER (CntyCd, PclId, PclSeqNbr, ChronoNbr DESC) INCLUDE(AvmValAmt, BeneficiaryPurchasedInd,  CltvPct, CurrIEQAmt, CurrIEQDt, CurrIEQPct, DeedCatTyp, DocTyp, EditCounter, EditReqdInd, EditTimestamp, InterrelatedInd, IrisLandUseCd, LegalMapTypCd, MultiSplitCd, NominalInd, OltvPct, OrigIeqAmt, OrigIeqDt, OrigIeqPct, OrigRecordingDtDerived, PartialInterestTransferInd, PriceCalcInd, PriceSqFtAmt, PrimaryCatCd, PropIndCd, RealEstateOwnedInd, RealEstateOwnedSaleInd, RecordingDt, RecordTyp, RejectInd, ResModelInd, SaleDt, SalePosition, SalePriceAmt, SecDCRealEstateOwnedSaleInd, SourceInd, StndAloneMtgInd, TaxStampAmt, TaxStampAmtCd, TransTyp, VestingPosition)
    
    SELECT  p.CntyCd
          , p.PclId
          , p.PclSeqNbr
          , p.SumNbrUnits
    INTO    Temporary.dbo.VTC_Property
    FROM    (SELECT b.CntyCd /* Alias Must Be b */
                  , p.PclId
                  , p.PclSeqNbr
                  , p.SumNbrUnits
             FROM   tTax.PROPERTY p
             INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON p.CntyCd = b.CntyCd) p
    WHERE   EXISTS ( SELECT *
                     FROM   Temporary.dbo.VTC_Trans_ER v
                     WHERE  p.CntyCd = v.CntyCd
                            AND p.PclId = v.PclId
                            AND p.PclSeqNbr = v.PclSeqNbr )
    
    CREATE UNIQUE CLUSTERED INDEX VTC_Property ON Temporary.dbo.VTC_Property (CntyCd, PclId, PclSeqNbr)
    
    SELECT  a.CntyCd
          , a.BatchDt
          , a.BatchSeq
          , a.AvmValAmt
          , a.BeneficiaryPurchasedInd
          , a.ChronoNbr
          , a.CltvPct
          , a.CurrIEQAmt
          , a.CurrIEQDt
          , a.CurrIEQPct
          , a.DeedCatTyp
          , a.DocTyp
          , a.EditCounter
          , a.EditReqdInd
          , a.EditTimestamp
          , a.InterrelatedInd
          , a.IrisLandUseCd
          , a.LegalMapTypCd
          , a.MultiSplitCd
          , a.NominalInd
          , a.OltvPct
          , a.OrigIeqAmt
          , a.OrigIeqDt
          , a.OrigIeqPct
          , a.OrigRecordingDtDerived
          , a.PartialInterestTransferInd
          , a.PclId
          , a.PclSeqNbr
          , a.PriceCalcInd
          , a.PriceSqFtAmt
          , a.PrimaryCatCd
          , a.PropIndCd
          , a.RealEstateOwnedInd
          , a.RealEstateOwnedSaleInd
          , a.RecordingDt
          , a.RecordTyp
          , a.RejectInd
          , a.ResModelInd
          , a.SaleDt
          , a.SalePosition
          , a.SalePriceAmt
          , a.SecDCRealEstateOwnedSaleInd
          , a.SourceInd
          , a.StndAloneMtgInd
          , a.TaxStampAmt
          , a.TaxStampAmtCd
          , a.TransTyp
          , a.VestingPosition
    INTO    Temporary.dbo.VTC_Trans
    FROM    (SELECT b.CntyCd /* Alias Must be b */
                  , a.BatchDt
                  , a.BatchSeq
                  , a.AvmValAmt
                  , a.BeneficiaryPurchasedInd
                  , a.ChronoNbr
                  , a.CltvPct
                  , a.CurrIEQAmt
                  , a.CurrIEQDt
                  , a.CurrIEQPct
                  , a.DeedCatTyp
                  , a.DocTyp
                  , a.EditCounter
                  , a.EditReqdInd
                  , a.EditTimestamp
                  , a.InterrelatedInd
                  , a.IrisLandUseCd
                  , a.LegalMapTypCd
                  , a.MultiSplitCd
                  , a.NominalInd
                  , a.OltvPct
                  , a.OrigIeqAmt
                  , a.OrigIeqDt
                  , a.OrigIeqPct
                  , a.OrigRecordingDtDerived
                  , a.PartialInterestTransferInd
                  , a.PclId
                  , a.PclSeqNbr
                  , a.PriceCalcInd
                  , a.PriceSqFtAmt
                  , a.PrimaryCatCd
                  , a.PropIndCd
                  , a.RealEstateOwnedInd
                  , a.RealEstateOwnedSaleInd
                  , a.RecordingDt
                  , a.RecordTyp
                  , a.RejectInd
                  , a.ResModelInd
                  , a.SaleDt
                  , a.SalePosition
                  , a.SalePriceAmt
                  , a.SecDCRealEstateOwnedSaleInd
                  , a.SourceInd
                  , a.StndAloneMtgInd
                  , a.TaxStampAmt
                  , a.TaxStampAmtCd
                  , a.TransTyp
                  , a.VestingPosition
             FROM   Trans.vTrans a
             INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON a.CntyCd = b.CntyCd) a
    WHERE   EXISTS ( SELECT *
                     FROM   Temporary.dbo.VTC_Trans_ER b
                     WHERE  a.CntyCd = b.CntyCd
                            AND a.PclId = b.PclId
                            AND a.PclSeqNbr = b.PclSeqNbr )
    
    						CREATE UNIQUE CLUSTERED INDEX VTC_Trans ON Temporary.dbo.VTC_Trans (CntyCd, BatchDt, BatchSeq)
    
    CREATE NONCLUSTERED INDEX VTC_Trans_PCL ON Temporary.dbo.VTC_Trans (CntyCd, PclId, PclSeqNbr, ChronoNbr DESC) INCLUDE(AvmValAmt, BeneficiaryPurchasedInd,  CltvPct, CurrIEQAmt, CurrIEQDt, CurrIEQPct, DeedCatTyp, DocTyp, EditCounter, EditReqdInd, EditTimestamp, InterrelatedInd, IrisLandUseCd, LegalMapTypCd, MultiSplitCd, NominalInd, OltvPct, OrigIeqAmt, OrigIeqDt, OrigIeqPct, OrigRecordingDtDerived, PartialInterestTransferInd, PriceCalcInd, PriceSqFtAmt, PrimaryCatCd, PropIndCd, RealEstateOwnedInd, RealEstateOwnedSaleInd, RecordingDt, RecordTyp, RejectInd, ResModelInd, SaleDt, SalePosition, SalePriceAmt, SecDCRealEstateOwnedSaleInd, SourceInd, StndAloneMtgInd, TaxStampAmt, TaxStampAmtCd, TransTyp, VestingPosition)
    
    SELECT  lp.CntyCd
          , lp.BatchDt
          , lp.BatchSeq
          , lp.NameTypId
          , lp.NameSeq
          , lp.CdTableTyp
          , lp.CdVal
          , lp.CorpInd
          , lp.FullName
          , lp.IsRedacted
          , lp.OwnrShipRightsCd
    INTO    Temporary.dbo.VTC_TransLegalPartyName
    FROM    (SELECT b.CntyCd /* Alias Must be b */
                  , lp.BatchDt
                  , lp.BatchSeq
                  , lp.NameTypId
                  , lp.NameSeq
                  , lp.CdTableTyp
                  , lp.CdVal
                  , lp.CorpInd
                  , lp.FullName
                  , lp.IsRedacted
                  , lp.OwnrShipRightsCd
             FROM   tTrans.TransLegalPartyName lp
             INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON lp.CntyCd = b.CntyCd) lp
    INNER JOIN Temporary.dbo.VTC_Trans t ON t.CntyCd = lp.CntyCd
                                            AND t.BatchDt = lp.BatchDt
                                            AND t.BatchSeq = lp.BatchSeq
    WHERE   NameTypId IN (1, 2)
    
    CREATE UNIQUE CLUSTERED INDEX VTC_TransLegalPartyName ON Temporary.dbo.VTC_TransLegalPartyName (CntyCd, BatchDt, BatchSeq,NameTypId,NameSeq)
    
    SELECT  lp.CntyCd
          , lp.BatchDt
          , lp.BatchSeq
          , lp.NameTypId
          , lp.NameSeq
          , lp.ParseSeq
          , lp.IsRedacted
          , lp.LastName
    INTO    Temporary.dbo.VTC_TransLegalPartyParsed
    FROM    (SELECT b.CntyCd /* Alias must be b */
                  , lp.BatchDt
                  , lp.BatchSeq
                  , lp.NameTypId
                  , lp.NameSeq
                  , lp.ParseSeq
                  , lp.IsRedacted
                  , lp.LastName
             FROM   tTrans.TransLegalPartyParsed lp
             INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON lp.CntyCd = b.CntyCd) lp
    INNER JOIN Temporary.dbo.VTC_Trans t ON t.CntyCd = lp.CntyCd
                                            AND t.BatchDt = lp.BatchDt
                                            AND t.BatchSeq = lp.BatchSeq
    WHERE   NameTypId = 2
            AND NameSeq = 1
            AND ParseSeq = 1
    
    CREATE UNIQUE CLUSTERED INDEX VTC_TransLegalPartyParsed ON Temporary.dbo.VTC_TransLegalPartyParsed (CntyCd, BatchDt, BatchSeq,NameTypId,NameSeq,ParseSeq)
    
    SELECT  tm.CntyCd
          , tm.BatchDt
          , tm.BatchSeq
          , tm.MtgSeq
          , tm.LndrFullName
          , tm.LndrLastName
          , tm.MtgAmt
          , tm.MtgAssumpAmtInd
          , tm.MtgCnstrLoanInd
          , tm.MtgCompanyCd
          , tm.MtgDocNbr
          , tm.MtgDocTyp
          , tm.MtgDt
          , tm.MtgIntRateTyp
          , tm.MtgLienPosition
          , tm.MtgLoanTypCd
          , tm.MtgRecordingPage
          , tm.MtgDocYyDt
          , tm.MtgModAmt
          , tm.MtgMostRecentInd
          , tm.MtgRecordingBook
          , tm.MtgRecordingDt
          , tm.MtgStatusInd
          , tm.MtgSubordTyp
          , tm.MtgTypInd
          , tm.SecMCEquityLoanInd
          , tm.SecMCOtherSubordLoanInd
          , tm.SecMCRefinanceLoanInd
    INTO    Temporary.dbo.VTC_TransMortgage
    FROM    (SELECT b.CntyCd /* Alias must be b */
                  , tm.BatchDt
                  , tm.BatchSeq
                  , tm.MtgSeq
                  , tm.LndrFullName
                  , tm.LndrLastName
                  , tm.MtgAmt
                  , tm.MtgAssumpAmtInd
                  , tm.MtgCnstrLoanInd
                  , tm.MtgCompanyCd
                  , tm.MtgDocNbr
                  , tm.MtgDocTyp
                  , tm.MtgDt
                  , tm.MtgIntRateTyp
                  , tm.MtgLienPosition
                  , tm.MtgLoanTypCd
                  , tm.MtgRecordingPage
                  , tm.MtgDocYyDt
                  , tm.MtgModAmt
                  , tm.MtgMostRecentInd
                  , tm.MtgRecordingBook
                  , tm.MtgRecordingDt
                  , tm.MtgStatusInd
                  , tm.MtgSubordTyp
                  , tm.MtgTypInd
                  , tm.SecMCEquityLoanInd
                  , tm.SecMCOtherSubordLoanInd
                  , tm.SecMCRefinanceLoanInd
             FROM   tTrans.TransMortgage tm
             INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON tm.CntyCd = b.CntyCd) tm
    INNER JOIN Temporary.dbo.VTC_Trans t ON t.CntyCd = tm.CntyCd
                                            AND t.BatchDt = tm.BatchDt
                                            AND t.BatchSeq = tm.BatchSeq
    
    CREATE UNIQUE CLUSTERED INDEX VTC_TransMortgage ON Temporary.dbo.VTC_TransMortgage (CntyCd, BatchDt, BatchSeq,MtgSeq)


    Gokhan Varol

    Sunday, June 17, 2012 12:02 AM
  • If you are interested check this out, seems to work so far. Notice the comment sections within the sql statements

    Gokhan, that's remarkable, and thanks for sharing!

    I'm going to guess that SQL is smart enough to look at the statistics for your temp table, see it only has 1,000 rows, and then does its partition elimination trick.  It was apparently NOT smart enough to do that, if the 1,000 was a result of a query against a larger table, even if statistics on it were available?  And you didn't even have to resort to dynamic SQL. :)

    But you never did answer the question before - how long does it take to execute when it looks at all 3,000 partitions, as opposed to when it does the partition elimination?

    Thanks, and again - congratulations!

    Josh

    Sunday, June 17, 2012 12:22 AM
  • Josh

    I did not save the numbers but the reason I started the exercise of eliminating partitions is I noticed (we have 3000+ partitions on the tables but some partitions belong to a small county and they may have 10K records and some partitions in LA may have 22Mil+ records, they are not homogeneous) the more partitions (or data) the join to the key table the longer the duration. I am playing around with the queries right now to bring down the total temp table creation duration to make our processes fit into the window they need to be and things do not look pretty so far.


    Gokhan Varol

    Sunday, June 17, 2012 3:25 AM
  • Josh

    Ignore the query I posted, it's still not eliminating on all the queries, I thought it did, now I am playing around with $PARTITION function if that would work, basically storing the partitionid in a table (with unique clustered index) and join to that. I had success with $PARTITION function in the past but did not last long, after running a scheduled job every 15 minutes using partition elimination with $PARTITION function the query started giving assertions after hundredts of times of execution, I checked the database with full DBCC which took 14 hours and no errors found and I had to back out of using $PARTITION function. This one is not as intense in $PARTITION usage and it will not be executed more than couple ten's times a day (I hope).

    Anyway nothing solid yet, I am just trying out.

    SELECT  lp.CntyCd
          , lp.BatchDt
          , lp.BatchSeq
          , lp.NameTypId
          , lp.NameSeq
          , lp.ParseSeq
          , lp.IsRedacted
          , lp.LastName
    INTO    Temporary.dbo.VTC_TransLegalPartyParsed
    FROM    (SELECT lp.CntyCd
                  , lp.BatchDt
                  , lp.BatchSeq
                  , lp.NameTypId
                  , lp.NameSeq
                  , lp.ParseSeq
                  , lp.IsRedacted
                  , lp.LastName
             FROM   tTrans.TransLegalPartyParsed lp
             INNER JOIN Temporary.dbo.PartitionIds_TransLegalPartyParsed b ON $PARTITION.pf_tTrans_TransLegalPartyParsed(lp.CntyCd) = b.BoundaryId) lp
    INNER JOIN Temporary.dbo.VTC_Trans t ON t.CntyCd = lp.CntyCd
                                            AND t.BatchDt = lp.BatchDt
                                            AND t.BatchSeq = lp.BatchSeq
    WHERE   NameTypId = 2
            AND NameSeq = 1
            AND ParseSeq = 1
    


    Gokhan Varol

    Sunday, June 17, 2012 4:33 AM
  • Josh

    I got some before after numbers.

    The answer is yes, elimination make a linear difference.


    Gokhan Varol


    • Edited by GV1973 Sunday, June 17, 2012 3:24 PM
    Sunday, June 17, 2012 3:21 PM
  • Josh

    using $Partition function seems to work every time. Below is the query that was on the right side of the numbers.

    USE Diablo
    GO
    IF OBJECT_ID('Temporary.dbo.VTC_Property') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_Property
    IF OBJECT_ID('Temporary.dbo.VTC_Trans_ER') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_Trans_ER
    IF OBJECT_ID('Temporary.dbo.VTC_Trans') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_Trans
    IF OBJECT_ID('Temporary.dbo.VTC_TransLegalPartyName') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_TransLegalPartyName
    IF OBJECT_ID('Temporary.dbo.VTC_TransLegalPartyParsed') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_TransLegalPartyParsed
    IF OBJECT_ID('Temporary.dbo.VTC_TransMortgage') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_TransMortgage
    
    
    IF OBJECT_ID('Temporary.dbo.PartitionIds_Property') IS NOT NULL 
        DROP TABLE  Temporary.dbo.PartitionIds_Property
    IF OBJECT_ID('Temporary.dbo.PartitionIds_Trans') IS NOT NULL 
        DROP TABLE  Temporary.dbo.PartitionIds_Trans
    IF OBJECT_ID('Temporary.dbo.PartitionIds_TransLegalPartyName') IS NOT NULL 
        DROP TABLE  Temporary.dbo.PartitionIds_TransLegalPartyName
    IF OBJECT_ID('Temporary.dbo.PartitionIds_TransLegalPartyParsed') IS NOT NULL 
        DROP TABLE  Temporary.dbo.PartitionIds_TransLegalPartyParsed
    IF OBJECT_ID('Temporary.dbo.PartitionIds_TransMortgage') IS NOT NULL 
        DROP TABLE  Temporary.dbo.PartitionIds_TransMortgage
    
    
    IF OBJECT_ID('Temporary.dbo.VTC_SampleDataCntyCd') IS NOT NULL 
        DROP TABLE  Temporary.dbo.VTC_SampleDataCntyCd
    
    SELECT DISTINCT
            CntyCd
    INTO    Temporary.dbo.VTC_SampleDataCntyCd
    FROM    Temporary.dbo.VTC_SampleData2
    
    CREATE UNIQUE CLUSTERED INDEX CntyCd ON Temporary.dbo.VTC_SampleDataCntyCd(CntyCd)
    
    
    --SELECT  a.BoundaryId
    --INTO    Temporary.dbo.PartitionIds_Property
    --FROM    Metadata.ObjectPartitionValues a
    --INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON a.PartitionValue = b.CntyCd
    --WHERE   a.objectid = OBJECT_ID('tTax.Property')
    --        AND a.IndexId = 1
    
    --CREATE UNIQUE CLUSTERED INDEX Property ON Temporary.dbo.PartitionIds_Property(BoundaryId)
    
    --SELECT  a.BoundaryId
    --INTO    Temporary.dbo.PartitionIds_Trans
    --FROM    Metadata.ObjectPartitionValues a
    --INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON a.PartitionValue = b.CntyCd
    --WHERE   a.objectid = OBJECT_ID('tTrans.Trans')
    --        AND a.IndexId = 1
    
    --CREATE UNIQUE CLUSTERED INDEX Trans ON Temporary.dbo.PartitionIds_Trans(BoundaryId)
    
    
    --SELECT  a.BoundaryId
    --INTO    Temporary.dbo.PartitionIds_TransLegalPartyName
    --FROM    Metadata.ObjectPartitionValues a
    --INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON a.PartitionValue = b.CntyCd
    --WHERE   a.objectid = OBJECT_ID('tTrans.TransLegalPartyName')
    --        AND a.IndexId = 1
    
    --CREATE UNIQUE CLUSTERED INDEX TransLegalPartyName ON Temporary.dbo.PartitionIds_TransLegalPartyName(BoundaryId)
    
    
    --SELECT  a.BoundaryId
    --INTO    Temporary.dbo.PartitionIds_TransLegalPartyParsed
    --FROM    Metadata.ObjectPartitionValues a
    --INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON a.PartitionValue = b.CntyCd
    --WHERE   a.objectid = OBJECT_ID('tTrans.TransLegalPartyParsed')
    --        AND a.IndexId = 1
    
    --CREATE UNIQUE CLUSTERED INDEX PartitionIds_TransLegalPartyParsed ON Temporary.dbo.PartitionIds_TransLegalPartyParsed(BoundaryId)
    
    --SELECT  a.BoundaryId
    --INTO    Temporary.dbo.PartitionIds_TransMortgage
    --FROM    Metadata.ObjectPartitionValues a
    --INNER JOIN Temporary.dbo.VTC_SampleDataCntyCd b ON a.PartitionValue = b.CntyCd
    --WHERE   a.objectid = OBJECT_ID('tTrans.TransMortgage')
    --        AND a.IndexId = 1
    
    --CREATE UNIQUE CLUSTERED INDEX PartitionIds_TransMortgage ON Temporary.dbo.PartitionIds_TransMortgage(BoundaryId)
    
    SELECT  a.CntyCd
          , a.BatchDt
          , a.BatchSeq
          , a.AvmValAmt
          , a.BeneficiaryPurchasedInd
          , a.ChronoNbr
          , a.CltvPct
          , a.CurrIEQAmt
          , a.CurrIEQDt
          , a.CurrIEQPct
          , a.DeedCatTyp
          , a.DocTyp
          , a.EditCounter
          , a.EditReqdInd
          , a.EditTimestamp
          , a.InterrelatedInd
          , a.IrisLandUseCd
          , a.LegalMapTypCd
          , a.MultiSplitCd
          , a.NominalInd
          , a.OltvPct
          , a.OrigIeqAmt
          , a.OrigIeqDt
          , a.OrigIeqPct
          , a.OrigRecordingDtDerived
          , a.PartialInterestTransferInd
          , a.PclId
          , a.PclSeqNbr
          , a.PriceCalcInd
          , a.PriceSqFtAmt
          , a.PrimaryCatCd
          , a.PropIndCd
          , a.RealEstateOwnedInd
          , a.RealEstateOwnedSaleInd
          , a.RecordingDt
          , a.RecordTyp
          , a.RejectInd
          , a.ResModelInd
          , a.SaleDt
          , a.SalePosition
          , a.SalePriceAmt
          , a.SecDCRealEstateOwnedSaleInd
          , a.SourceInd
          , a.StndAloneMtgInd
          , a.TaxStampAmt
          , a.TaxStampAmtCd
          , a.TransTyp
          , a.VestingPosition
    INTO    Temporary.dbo.VTC_Trans_ER
    FROM    (SELECT a.CntyCd
                  , a.BatchDt
                  , a.BatchSeq
                  , a.AvmValAmt
                  , a.BeneficiaryPurchasedInd
                  , a.ChronoNbr
                  , a.CltvPct
                  , a.CurrIEQAmt
                  , a.CurrIEQDt
                  , a.CurrIEQPct
                  , a.DeedCatTyp
                  , a.DocTyp
                  , a.EditCounter
                  , a.EditReqdInd
                  , a.EditTimestamp
                  , a.InterrelatedInd
                  , a.IrisLandUseCd
                  , a.LegalMapTypCd
                  , a.MultiSplitCd
                  , a.NominalInd
                  , a.OltvPct
                  , a.OrigIeqAmt
                  , a.OrigIeqDt
                  , a.OrigIeqPct
                  , a.OrigRecordingDtDerived
                  , a.PartialInterestTransferInd
                  , a.PclId
                  , a.PclSeqNbr
                  , a.PriceCalcInd
                  , a.PriceSqFtAmt
                  , a.PrimaryCatCd
                  , a.PropIndCd
                  , a.RealEstateOwnedInd
                  , a.RealEstateOwnedSaleInd
                  , a.RecordingDt
                  , a.RecordTyp
                  , a.RejectInd
                  , a.ResModelInd
                  , a.SaleDt
                  , a.SalePosition
                  , a.SalePriceAmt
                  , a.SecDCRealEstateOwnedSaleInd
                  , a.SourceInd
                  , a.StndAloneMtgInd
                  , a.TaxStampAmt
                  , a.TaxStampAmtCd
                  , a.TransTyp
                  , a.VestingPosition
             FROM   Trans.vTrans a
             WHERE  EXISTS ( SELECT 1
                             FROM   Temporary.dbo.VTC_SampleDataCntyCd b
                             WHERE  $PARTITION.pf_tTrans_Trans(a.CntyCd) = $PARTITION.pf_tTrans_Trans(b.CntyCd) )) a
    INNER JOIN Temporary.dbo.VTC_SampleData2 b ON a.CntyCd = b.CntyCd
                                                  AND a.BatchDt = b.BatchDt
                                                  AND a.BatchSeq = b.BatchSeq
    WHERE   1 = 1
    
    CREATE UNIQUE CLUSTERED INDEX VTC_Trans_ER ON Temporary.dbo.VTC_Trans_ER (CntyCd, BatchDt, BatchSeq)
    
    CREATE NONCLUSTERED INDEX VTC_Trans_PCL ON Temporary.dbo.VTC_Trans_ER (CntyCd, PclId, PclSeqNbr, ChronoNbr DESC) INCLUDE(AvmValAmt, BeneficiaryPurchasedInd,  CltvPct, CurrIEQAmt, CurrIEQDt, CurrIEQPct, DeedCatTyp, DocTyp, EditCounter, EditReqdInd, EditTimestamp, InterrelatedInd, IrisLandUseCd, LegalMapTypCd, MultiSplitCd, NominalInd, OltvPct, OrigIeqAmt, OrigIeqDt, OrigIeqPct, OrigRecordingDtDerived, PartialInterestTransferInd, PriceCalcInd, PriceSqFtAmt, PrimaryCatCd, PropIndCd, RealEstateOwnedInd, RealEstateOwnedSaleInd, RecordingDt, RecordTyp, RejectInd, ResModelInd, SaleDt, SalePosition, SalePriceAmt, SecDCRealEstateOwnedSaleInd, SourceInd, StndAloneMtgInd, TaxStampAmt, TaxStampAmtCd, TransTyp, VestingPosition)
    
    SELECT  p.CntyCd
          , p.PclId
          , p.PclSeqNbr
          , p.SumNbrUnits
    INTO    Temporary.dbo.VTC_Property
    FROM    (SELECT p.CntyCd
                  , p.PclId
                  , p.PclSeqNbr
                  , p.SumNbrUnits
             FROM   tTax.Property p
             WHERE  EXISTS ( SELECT 1
                             FROM   Temporary.dbo.VTC_SampleDataCntyCd b
                             WHERE  $PARTITION.pf_tTax_Property(p.CntyCd) = $PARTITION.pf_tTax_Property(b.CntyCd) )) p
    WHERE   EXISTS ( SELECT *
                     FROM   Temporary.dbo.VTC_Trans_ER v
                     WHERE  p.CntyCd = v.CntyCd
                            AND p.PclId = v.PclId
                            AND p.PclSeqNbr = v.PclSeqNbr )
            AND 1 = 1
    
    CREATE UNIQUE CLUSTERED INDEX VTC_Property ON Temporary.dbo.VTC_Property (CntyCd, PclId, PclSeqNbr)
    
    SELECT  a.CntyCd
          , a.BatchDt
          , a.BatchSeq
          , a.AvmValAmt
          , a.BeneficiaryPurchasedInd
          , a.ChronoNbr
          , a.CltvPct
          , a.CurrIEQAmt
          , a.CurrIEQDt
          , a.CurrIEQPct
          , a.DeedCatTyp
          , a.DocTyp
          , a.EditCounter
          , a.EditReqdInd
          , a.EditTimestamp
          , a.InterrelatedInd
          , a.IrisLandUseCd
          , a.LegalMapTypCd
          , a.MultiSplitCd
          , a.NominalInd
          , a.OltvPct
          , a.OrigIeqAmt
          , a.OrigIeqDt
          , a.OrigIeqPct
          , a.OrigRecordingDtDerived
          , a.PartialInterestTransferInd
          , a.PclId
          , a.PclSeqNbr
          , a.PriceCalcInd
          , a.PriceSqFtAmt
          , a.PrimaryCatCd
          , a.PropIndCd
          , a.RealEstateOwnedInd
          , a.RealEstateOwnedSaleInd
          , a.RecordingDt
          , a.RecordTyp
          , a.RejectInd
          , a.ResModelInd
          , a.SaleDt
          , a.SalePosition
          , a.SalePriceAmt
          , a.SecDCRealEstateOwnedSaleInd
          , a.SourceInd
          , a.StndAloneMtgInd
          , a.TaxStampAmt
          , a.TaxStampAmtCd
          , a.TransTyp
          , a.VestingPosition
    INTO    Temporary.dbo.VTC_Trans
    FROM    (SELECT a.CntyCd
                  , a.BatchDt
                  , a.BatchSeq
                  , a.AvmValAmt
                  , a.BeneficiaryPurchasedInd
                  , a.ChronoNbr
                  , a.CltvPct
                  , a.CurrIEQAmt
                  , a.CurrIEQDt
                  , a.CurrIEQPct
                  , a.DeedCatTyp
                  , a.DocTyp
                  , a.EditCounter
                  , a.EditReqdInd
                  , a.EditTimestamp
                  , a.InterrelatedInd
                  , a.IrisLandUseCd
                  , a.LegalMapTypCd
                  , a.MultiSplitCd
                  , a.NominalInd
                  , a.OltvPct
                  , a.OrigIeqAmt
                  , a.OrigIeqDt
                  , a.OrigIeqPct
                  , a.OrigRecordingDtDerived
                  , a.PartialInterestTransferInd
                  , a.PclId
                  , a.PclSeqNbr
                  , a.PriceCalcInd
                  , a.PriceSqFtAmt
                  , a.PrimaryCatCd
                  , a.PropIndCd
                  , a.RealEstateOwnedInd
                  , a.RealEstateOwnedSaleInd
                  , a.RecordingDt
                  , a.RecordTyp
                  , a.RejectInd
                  , a.ResModelInd
                  , a.SaleDt
                  , a.SalePosition
                  , a.SalePriceAmt
                  , a.SecDCRealEstateOwnedSaleInd
                  , a.SourceInd
                  , a.StndAloneMtgInd
                  , a.TaxStampAmt
                  , a.TaxStampAmtCd
                  , a.TransTyp
                  , a.VestingPosition
             FROM   Trans.vTrans a
             WHERE  EXISTS ( SELECT 1
                             FROM   Temporary.dbo.VTC_SampleDataCntyCd b
                             WHERE  $PARTITION.pf_tTrans_Trans(a.CntyCd) = $PARTITION.pf_tTrans_Trans(b.CntyCd) )) a
    WHERE   EXISTS ( SELECT *
                     FROM   Temporary.dbo.VTC_Trans_ER b
                     WHERE  a.CntyCd = b.CntyCd
                            AND a.PclId = b.PclId
                            AND a.PclSeqNbr = b.PclSeqNbr )
            AND 1 = 1
    
    CREATE UNIQUE CLUSTERED INDEX VTC_Trans ON Temporary.dbo.VTC_Trans (CntyCd, BatchDt, BatchSeq)
    
    CREATE NONCLUSTERED INDEX VTC_Trans_PCL ON Temporary.dbo.VTC_Trans (CntyCd, PclId, PclSeqNbr, ChronoNbr DESC) INCLUDE(AvmValAmt, BeneficiaryPurchasedInd,  CltvPct, CurrIEQAmt, CurrIEQDt, CurrIEQPct, DeedCatTyp, DocTyp, EditCounter, EditReqdInd, EditTimestamp, InterrelatedInd, IrisLandUseCd, LegalMapTypCd, MultiSplitCd, NominalInd, OltvPct, OrigIeqAmt, OrigIeqDt, OrigIeqPct, OrigRecordingDtDerived, PartialInterestTransferInd, PriceCalcInd, PriceSqFtAmt, PrimaryCatCd, PropIndCd, RealEstateOwnedInd, RealEstateOwnedSaleInd, RecordingDt, RecordTyp, RejectInd, ResModelInd, SaleDt, SalePosition, SalePriceAmt, SecDCRealEstateOwnedSaleInd, SourceInd, StndAloneMtgInd, TaxStampAmt, TaxStampAmtCd, TransTyp, VestingPosition)
    
    SELECT  lp.CntyCd
          , lp.BatchDt
          , lp.BatchSeq
          , lp.NameTypId
          , lp.NameSeq
          , lp.CdTableTyp
          , lp.CdVal
          , lp.CorpInd
          , lp.FullName
          , lp.IsRedacted
          , lp.OwnrShipRightsCd
    INTO    Temporary.dbo.VTC_TransLegalPartyName
    FROM    (SELECT lp.CntyCd
                  , lp.BatchDt
                  , lp.BatchSeq
                  , lp.NameTypId
                  , lp.NameSeq
                  , lp.CdTableTyp
                  , lp.CdVal
                  , lp.CorpInd
                  , lp.FullName
                  , lp.IsRedacted
                  , lp.OwnrShipRightsCd
             FROM   tTrans.TransLegalPartyName lp
             WHERE  EXISTS ( SELECT 1
                             FROM   Temporary.dbo.VTC_SampleDataCntyCd b
                             WHERE  $PARTITION.pf_tTrans_TransLegalPartyName(lp.CntyCd) = $PARTITION.pf_tTrans_TransLegalPartyName(b.CntyCd) )) lp
    INNER JOIN Temporary.dbo.VTC_Trans t ON t.CntyCd = lp.CntyCd
                                            AND t.BatchDt = lp.BatchDt
                                            AND t.BatchSeq = lp.BatchSeq
    WHERE   NameTypId IN (1, 2)
            AND 1 = 1
    
    CREATE UNIQUE CLUSTERED INDEX VTC_TransLegalPartyName ON Temporary.dbo.VTC_TransLegalPartyName (CntyCd, BatchDt, BatchSeq,NameTypId,NameSeq)
    
    SELECT  lp.CntyCd
          , lp.BatchDt
          , lp.BatchSeq
          , lp.NameTypId
          , lp.NameSeq
          , lp.ParseSeq
          , lp.IsRedacted
          , lp.LastName
    INTO    Temporary.dbo.VTC_TransLegalPartyParsed
    FROM    (SELECT lp.CntyCd
                  , lp.BatchDt
                  , lp.BatchSeq
                  , lp.NameTypId
                  , lp.NameSeq
                  , lp.ParseSeq
                  , lp.IsRedacted
                  , lp.LastName
             FROM   tTrans.TransLegalPartyParsed lp
             WHERE  EXISTS ( SELECT 1
                             FROM   Temporary.dbo.VTC_SampleDataCntyCd b
                             WHERE  $PARTITION.pf_tTrans_TransLegalPartyParsed(lp.CntyCd) = $PARTITION.pf_tTrans_TransLegalPartyParsed(b.CntyCd) )) lp
    INNER JOIN Temporary.dbo.VTC_Trans t ON t.CntyCd = lp.CntyCd
                                            AND t.BatchDt = lp.BatchDt
                                            AND t.BatchSeq = lp.BatchSeq
    WHERE   NameTypId = 2
            AND NameSeq = 1
            AND ParseSeq = 1
            AND 1 = 1
    
    CREATE UNIQUE CLUSTERED INDEX VTC_TransLegalPartyParsed ON Temporary.dbo.VTC_TransLegalPartyParsed (CntyCd, BatchDt, BatchSeq,NameTypId,NameSeq,ParseSeq)
    
    SELECT  tm.CntyCd
          , tm.BatchDt
          , tm.BatchSeq
          , tm.MtgSeq
          , tm.LndrFullName
          , tm.LndrLastName
          , tm.MtgAmt
          , tm.MtgAssumpAmtInd
          , tm.MtgCnstrLoanInd
          , tm.MtgCompanyCd
          , tm.MtgDocNbr
          , tm.MtgDocTyp
          , tm.MtgDt
          , tm.MtgIntRateTyp
          , tm.MtgLienPosition
          , tm.MtgLoanTypCd
          , tm.MtgRecordingPage
          , tm.MtgDocYyDt
          , tm.MtgModAmt
          , tm.MtgMostRecentInd
          , tm.MtgRecordingBook
          , tm.MtgRecordingDt
          , tm.MtgStatusInd
          , tm.MtgSubordTyp
          , tm.MtgTypInd
          , tm.SecMCEquityLoanInd
          , tm.SecMCOtherSubordLoanInd
          , tm.SecMCRefinanceLoanInd
    INTO    Temporary.dbo.VTC_TransMortgage
    FROM    (SELECT tm.CntyCd
                  , tm.BatchDt
                  , tm.BatchSeq
                  , tm.MtgSeq
                  , tm.LndrFullName
                  , tm.LndrLastName
                  , tm.MtgAmt
                  , tm.MtgAssumpAmtInd
                  , tm.MtgCnstrLoanInd
                  , tm.MtgCompanyCd
                  , tm.MtgDocNbr
                  , tm.MtgDocTyp
                  , tm.MtgDt
                  , tm.MtgIntRateTyp
                  , tm.MtgLienPosition
                  , tm.MtgLoanTypCd
                  , tm.MtgRecordingPage
                  , tm.MtgDocYyDt
                  , tm.MtgModAmt
                  , tm.MtgMostRecentInd
                  , tm.MtgRecordingBook
                  , tm.MtgRecordingDt
                  , tm.MtgStatusInd
                  , tm.MtgSubordTyp
                  , tm.MtgTypInd
                  , tm.SecMCEquityLoanInd
                  , tm.SecMCOtherSubordLoanInd
                  , tm.SecMCRefinanceLoanInd
             FROM   tTrans.TransMortgage tm
             WHERE  EXISTS ( SELECT 1
                             FROM   Temporary.dbo.VTC_SampleDataCntyCd b
                             WHERE  $PARTITION.pf_tTrans_TransMortgage(tm.CntyCd) = $PARTITION.pf_tTrans_TransMortgage(b.CntyCd) )) tm
    INNER JOIN Temporary.dbo.VTC_Trans t ON t.CntyCd = tm.CntyCd
                                            AND t.BatchDt = tm.BatchDt
                                            AND t.BatchSeq = tm.BatchSeq
    WHERE   1 = 1
    
    
    CREATE UNIQUE CLUSTERED INDEX VTC_TransMortgage ON Temporary.dbo.VTC_TransMortgage (CntyCd, BatchDt, BatchSeq,MtgSeq)


    Gokhan Varol

    Sunday, June 17, 2012 3:23 PM
  • Gokhan, I don't grok this.  You're only showing the population of your temp tables, right?  Can't see why it should matter how they are populated.

    If the partition elimination improves performance linearly, then I assume (maybe you knew, or said?!) that the update code is scanning the partitions it does touch, because any keyed access would seem to be much faster on non-matching partitions.  That would seem to be a problem.  I know it often is with SQL Server, especially as applications scale up.  Or even worse, SQL Server can be correct that scanning is optimal, it's just slow anyway!  But it's never optimal to scan a partition that should be skipped.

    Josh

    Sunday, June 17, 2012 4:35 PM
  • If I run my process (not listed here) against full tables it takes over 7000 seconds to complete, if I run against temporary tables it takes 32 seconds to complete, temporary tables have a small subset of the columns and small subset of the rows and more in numbers and more fresh indexes. Unfortunately even the section that creates the temp tables has to be tuned since even that was taking over 4500 seconds, now eliminating partitions it takes about 2000 seconds, still not flying but I might be able to get the whole process done within an hour including the updates (the updates are not written yet, they may change the whole game plan depending on the performance).

    I have a list of keys (from 1000 partitions 200K sample keys to mimic the requirements) and with those I was trying to mimic 6 tables (only the columns and rows needed for the process to complete by replacing the table names to the temp tables and leaving the rest of the code not impacted).


    Gokhan Varol

    Sunday, June 17, 2012 4:42 PM
  • Basically the question since the beginning is how to get the data into smaller temp tables from 1000 random partitions and 200K rows out of close to a billion row table with over 3250 partitions (table size close to a TB) quicker and do it from 6 separate tables (smallest table has 200 mil rows largest table has about billion rows).

    I might have not explained this well up front, sorry if so.


    Gokhan Varol

    Sunday, June 17, 2012 4:49 PM
  • Basically the question since the beginning is how to get the data into smaller temp tables from 1000 random partitions and 200K rows out of close to a billion row table with over 3250 partitions (table size close to a TB) quicker and do it from 6 separate tables (smallest table has 200 mil rows largest table has about billion rows).

    I might have not explained this well up front, sorry if so.

    But don't you have to put the 200k rows back into the giant partitioned permanent tables at the end?

    I though that was where the bottleneck was.

    Josh

    Sunday, June 17, 2012 6:07 PM
  • Yes. there are basically 200K parent records that will create a push down affect. Those records needs to be processed through some complex logic (the complex logic has over 40 table joins and 3 single row table valued (60 - 80 cols input and 80-80 cols out and some of these are in and out) multi input clr functions. This process will generate all changed columns and keys out so that those have to be joined to the 3 tables that will be modified and modify them in a transaction. To run the process that generates the changes on the main tables takes over 7000 seconds. I either have to split the query into many steps which will be cumbersome and not easily maintainable or create a smaller tables to work by the process (that's what I am trying to do).

    The update will spread out to 1000 partitions again, which I do not expect to be fast either, but at least there will be only 1 join to the indexed temporary output table and update (repeat for 3 tables). I did not write the update yet.

    And the humor is my boss thinks clr solution is too complex and not maintainable and he wants me to rewrite it all in ssis (transfer data to a different machine, do the joins in ssis and then pass them through multiple script tasks and more joins etc), I am just laughing inside to this. He thinks this process will be done in 15-20 minutes. I am testing it on the production server with nothing else running on it and I might get it completed in an hour with nobody locking or using resources during the time being, which will be the best case.


    Gokhan Varol

    Sunday, June 17, 2012 6:22 PM
  • This is the initial process I am trying to speed up. Partition elimination gets trickier in a more complex query


    Gokhan Varol

    Sunday, June 17, 2012 6:29 PM
  • Say you compute the 200k rows wherever, putting them back into the big tables should run at no worse than 1,000/second, I think.  You might still need to generate 1,000 dynamic SQL statements to get them there, one statement per partition, to get SQL Server to run efficiently.

    Maybe your CLR routine, called from 1,000 statements, would get the whole thing done in little more time, say ten minutes (600 seconds).

    Did you ever get a full performance measure on the CLR, with all those parameters that might suck up CPU?

    Josh

    Sunday, June 17, 2012 11:50 PM
  • When I tested the clr against millions rows against a dataset before and after using clr there were literally no difference in cpu usage, I guess on clr side I am good. I have tried running queries per partition instead of the whole batch, and that was slow as a dog, you want to run them as a batch, only split them if they are too big and creating too much log or transactions becoming too large etc. I am restoring the production database to a different server , where I am going to create some indexes and test the update duration etc, taking a while

    Gokhan Varol

    Monday, June 18, 2012 1:08 AM
  • Josh

    This query works every time, eliminates to the right partition and uses clustered index seek on the large partitioned table and on a warm sql server is very fast and after detaching and attaching the database and so forth cleaning the cache for the database is slower( couple seconds vs 5-8 minutes with physical io) but still lots quicker than any other method. If you only use the cross apply without the top clause then query optimizer can (and will) convert it to a simpler join and do clustered index scan instead, performs bad, by having the top clause it uses the clustered index seek.

    Temporary.dbo.VTC_Trans is not partitioned has the keys (200k) and a unique index , I call this driver table
    tTrans.TransLegalPartyName is big (close to a billion rows and 3300+ partitions) and large partitioned table

    SELECT  lp.CntyCd
          , lp.BatchDt
          , lp.BatchSeq
          , lp.NameTypId
          , lp.NameSeq
          , lp.CdTableTyp
          , lp.CdVal
          , lp.CorpInd
          , lp.FullName
          , lp.IsRedacted
          , lp.OwnrShipRightsCd
    FROM    Temporary.dbo.VTC_Trans t
    CROSS APPLY (SELECT TOP 9999999999
                        *
                 FROM   tTrans.TransLegalPartyName lp
                 WHERE  t.CntyCd = lp.CntyCd
                        AND t.BatchDt = lp.BatchDt
                        AND t.BatchSeq = lp.BatchSeq
                        AND NameTypId IN (1, 2)) lp



    Gokhan Varol

    Wednesday, June 20, 2012 2:58 AM
  • This query works every time, eliminates to the right partition and uses clustered index seek on the large partitioned table and on a warm sql server is very fast and after detaching and attaching the database and so forth cleaning the cache for the database is slower( couple seconds vs 5-8 minutes with physical io) but still lots quicker than any other method. If you only use the cross apply without the top clause then query optimizer can (and will) convert it to a simpler join and do clustered index scan instead, performs bad, by having the top clause it uses the clustered index seek.

    That's a very interesting piece of code you've got there!  Cross Apply?  Top?

    I wonder if that would have worked for something I was trying a few months back ...

    Any hints as to sources or references you used to arrive at this?

    Cross Apply from the temp table forcing seek, hmm, ...

    ... any way to get the same trick to work on your Update statement!?!?

    Josh

    Wednesday, June 20, 2012 3:54 AM
  • Personally I don't see in the JOIN criteria, any condition where partition elimination should occur based on all of the queries that you have presented.  You are doing INNER JOIN operations to the $PARTITION function output but not further limiting the results, so that JOIN is essentially ignored by the optimizer from what I can tell.  This is something that Kimberly covers in our Immersion Event Week 1 course if I recall correctly, as an anti-pattern to partition elimination, but I've forwarded this thread along to a good friend Paul White, who understands the internals of the Query Optimizer better than anyone outside of Microsoft and perhaps he can chime in with some further specifics as to why you aren't getting partition elimination from a query that isn't limiting partitions in its basic logic.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, June 20, 2012 6:21 AM
  • Hi Jonathan

    $PARTITION does do the job, unfortunately it's generation assertions after running overnight on sql 2008 ent sp2 as a scheduled job every 30 minutes (and every time after that), it's probably related to us having 3300+ entries in partition functions and having slightly over billion of records on some tables (or not , did not feel like creating a connect ticket for this, since we need a solution now). That's why I had to find another way (and hopefully easier to write for users than dba's, they may not spell the right partition function name etc). In the sample above CntyCd char(5) partition key, BatchDt int, BatchSeq int are a natural primary key. I don't have the extensive knowledge of the query optimizer but this came out after many iterations of trying things.


    Gokhan Varol

    Wednesday, June 20, 2012 12:42 PM
  • Johs

    Update works the same way. Notice the top 99999999 (or whatever) actually returns either 1 or less than 10 records in my case, I just put something really large to work with whatever regardless


    Gokhan Varol

    Wednesday, June 20, 2012 12:43 PM
  • This query works every time, eliminates to the right partition and uses clustered index seek on the large partitioned table and on a warm sql server is very fast and after detaching and attaching the database and so forth cleaning the cache for the database is slower( couple seconds vs 5-8 minutes with physical io) but still lots quicker than any other method. If you only use the cross apply without the top clause then query optimizer can (and will) convert it to a simpler join and do clustered index scan instead, performs bad, by having the top clause it uses the clustered index seek.

    It would be better to use a FORCESEEK hint here, rather than CROSS APPLY and TOP.  Query hints (and the like) are the preferred mechanisms to help the optimizer find a good plan when your data does not fit its model.  Using the APPLY & TOP syntax may result in the execution plan you want today, but it is not guaranteed to do so.  Remember what happened to TOP (100) PERCENT.

    The core issue is that the optimizer's costing model does not fit your data and hardware in this specific case.  It estimates that scanning the whole table (taking advantage of read-ahead and sequential I/O) will be cheaper than performing 200,000 seeks.  It turns out that you see much better performance with the seeks than the scan, so the correct override is to force the seek.  Naturally, 200,000 seeks will not make as efficient use of read-ahead and sequential I/O as the scan (even accounting for nested loops pre-fetching), but you seem willing to make that trade-off.

    On the issue of partition elimination, what you are seeing with the seeks is dynamic partition elimination.  The RangePartitionNew function inside the seek calculates the correct partition id for every outer row at runtime.  This mechanism is generally available only nested loops joins (not hash or merge), since the correct partition for the seek needs to be calculated once per outer row.

    More generally, it seems that you are looking to use partitioning for performance reasons.  This is generally an error, in my experience.  There are all sorts of little issues and complications (that you are only beginning to encounter) which mean that partitioning is, generally speaking, best not used as a performance-enhancing feature, rather as a manageability one.  It may seem natural and 'obvious' that eliminating whole partitions would benefit performance, but this is generally not true, given correct design and optimal non-partitioned indexing in particular.

    Going beyond the 1,000 partition limit using the (somewhat shaky) extensions added in a Service Pack seems a high-risk strategy to me.  If I were forced to consider this level of partitioning, I would certainly want to be doing it on SQL Server 2012.  It's also worth noting that you are a full Service Pack behind current for SQL Server 2008, which might help explain the assertions (perhaps).  I will stress again though that I think (this level of) partitioning is an error, given what you have said about your needs in this and previous threads (unless, perhaps, partitioning on CntyCd gives you significant manageability benefits).

    I would be very tempted to stop now, and re-think the whole design rather than continuing to experiment like this.  It is certainly possible (with great care and expertise) to craft a high-performance system of this sort in T-SQL and SQLCLR, and it is equally possible to do it using SSIS.  The Data Loading Performance Guide has some good information and considerations, with and without partitioning.  There are a large number of other resources out there too, including experts who have real-world experience of doing this sort of thing on even larger systems than yours.

    As constructive feedback for your next question, try to ask your question more simply and precisely next time.  Including sample code that people without access to your databases can use directly will also help a great deal.  Providing a statistics-only copy of the tables in question is a great option, and much easier to work with for us.  There are a number of online facilities to make large scripts (and *.sqlplan files rather than images!) available (e.g. pastebin), which can make posts here more concise as well.

    Finally, here's a demo based loosely on the sort of data structures you appear to have, showing the parallel nested-loops plan with dynamic partition elimination, and the optional FORCESEEK hint:

    USE Sandpit;
    GO
    CREATE PARTITION FUNCTION PF (integer)
    AS RANGE RIGHT
    FOR VALUES
        (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,1193,1194,1195,1196,1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1239,1240,1241,1242,1243,1244,1245,1246,1247,1248,1249,1250,1251,1252,1253,1254,1255,1256,1257,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1280,1281,1282,1283,1284,1285,1286,1287,1288,1289,1290,1291,1292,1293,1294,1295,1296,1297,1298,1299,1300,1301,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330,1331,1332,1333,1334,1335,1336,1337,1338,1339,1340,1341,1342,1343,1344,1345,1346,1347,1348,1349,1350,1351,1352,1353,1354,1355,1356,1357,1358,1359,1360,1361,1362,1363,1364,1365,1366,1367,1368,1369,1370,1371,1372,1373,1374,1375,1376,1377,1378,1379,1380,1381,1382,1383,1384,1385,1386,1387,1388,1389,1390,1391,1392,1393,1394,1395,1396,1397,1398,1399,1400,1401,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445,1446,1447,1448,1449,1450,1451,1452,1453,1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1527,1528,1529,1530,1531,1532,1533,1534,1535,1536,1537,1538,1539,1540,1541,1542,1543,1544,1545,1546,1547,1548,1549,1550,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,1568,1569,1570,1571,1572,1573,1574,1575,1576,1577,1578,1579,1580,1581,1582,1583,1584,1585,1586,1587,1588,1589,1590,1591,1592,1593,1594,1595,1596,1597,1598,1599,1600,1601,1602,1603,1604,1605,1606,1607,1608,1609,1610,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641,1642,1643,1644,1645,1646,1647,1648,1649,1650,1651,1652,1653,1654,1655,1656,1657,1658,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,1674,1675,1676,1677,1678,1679,1680,1681,1682,1683,1684,1685,1686,1687,1688,1689,1690,1691,1692,1693,1694,1695,1696,1697,1698,1699,1700,1701,1702,1703,1704,1705,1706,1707,1708,1709,1710,1711,1712,1713,1714,1715,1716,1717,1718,1719,1720,1721,1722,1723,1724,1725,1726,1727,1728,1729,1730,1731,1732,1733,1734,1735,1736,1737,1738,1739,1740,1741,1742,1743,1744,1745,1746,1747,1748,1749,1750,1751,1752,1753,1754,1755,1756,1757,1758,1759,1760,1761,1762,1763,1764,1765,1766,1767,1768,1769,1770,1771,1772,1773,1774,1775,1776,1777,1778,1779,1780,1781,1782,1783,1784,1785,1786,1787,1788,1789,1790,1791,1792,1793,1794,1795,1796,1797,1798,1799,1800,1801,1802,1803,1804,1805,1806,1807,1808,1809,1810,1811,1812,1813,1814,1815,1816,1817,1818,1819,1820,1821,1822,1823,1824,1825,1826,1827,1828,1829,1830,1831,1832,1833,1834,1835,1836,1837,1838,1839,1840,1841,1842,1843,1844,1845,1846,1847,1848,1849,1850,1851,1852,1853,1854,1855,1856,1857,1858,1859,1860,1861,1862,1863,1864,1865,1866,1867,1868,1869,1870,1871,1872,1873,1874,1875,1876,1877,1878,1879,1880,1881,1882,1883,1884,1885,1886,1887,1888,1889,1890,1891,1892,1893,1894,1895,1896,1897,1898,1899,1900,1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911,1912,1913,1914,1915,1916,1917,1918,1919,1920,1921,1922,1923,1924,1925,1926,1927,1928,1929,1930,1931,1932,1933,1934,1935,1936,1937,1938,1939,1940,1941,1942,1943,1944,1945,1946,1947,1948,1949,1950,1951,1952,1953,1954,1955,1956,1957,1958,1959,1960,1961,1962,1963,1964,1965,1966,1967,1968,1969,1970,1971,1972,1973,1974,1975,1976,1977,1978,1979,1980,1981,1982,1983,1984,1985,1986,1987,1988,1989,1990,1991,1992,1993,1994,1995,1996,1997,1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2043,2044,2045,2046,2047,2048,2049,2050,2051,2052,2053,2054,2055,2056,2057,2058,2059,2060,2061,2062,2063,2064,2065,2066,2067,2068,2069,2070,2071,2072,2073,2074,2075,2076,2077,2078,2079,2080,2081,2082,2083,2084,2085,2086,2087,2088,2089,2090,2091,2092,2093,2094,2095,2096,2097,2098,2099,2100,2101,2102,2103,2104,2105,2106,2107,2108,2109,2110,2111,2112,2113,2114,2115,2116,2117,2118,2119,2120,2121,2122,2123,2124,2125,2126,2127,2128,2129,2130,2131,2132,2133,2134,2135,2136,2137,2138,2139,2140,2141,2142,2143,2144,2145,2146,2147,2148,2149,2150,2151,2152,2153,2154,2155,2156,2157,2158,2159,2160,2161,2162,2163,2164,2165,2166,2167,2168,2169,2170,2171,2172,2173,2174,2175,2176,2177,2178,2179,2180,2181,2182,2183,2184,2185,2186,2187,2188,2189,2190,2191,2192,2193,2194,2195,2196,2197,2198,2199,2200,2201,2202,2203,2204,2205,2206,2207,2208,2209,2210,2211,2212,2213,2214,2215,2216,2217,2218,2219,2220,2221,2222,2223,2224,2225,2226,2227,2228,2229,2230,2231,2232,2233,2234,2235,2236,2237,2238,2239,2240,2241,2242,2243,2244,2245,2246,2247,2248,2249,2250,2251,2252,2253,2254,2255,2256,2257,2258,2259,2260,2261,2262,2263,2264,2265,2266,2267,2268,2269,2270,2271,2272,2273,2274,2275,2276,2277,2278,2279,2280,2281,2282,2283,2284,2285,2286,2287,2288,2289,2290,2291,2292,2293,2294,2295,2296,2297,2298,2299,2300,2301,2302,2303,2304,2305,2306,2307,2308,2309,2310,2311,2312,2313,2314,2315,2316,2317,2318,2319,2320,2321,2322,2323,2324,2325,2326,2327,2328,2329,2330,2331,2332,2333,2334,2335,2336,2337,2338,2339,2340,2341,2342,2343,2344,2345,2346,2347,2348,2349,2350,2351,2352,2353,2354,2355,2356,2357,2358,2359,2360,2361,2362,2363,2364,2365,2366,2367,2368,2369,2370,2371,2372,2373,2374,2375,2376,2377,2378,2379,2380,2381,2382,2383,2384,2385,2386,2387,2388,2389,2390,2391,2392,2393,2394,2395,2396,2397,2398,2399,2400,2401,2402,2403,2404,2405,2406,2407,2408,2409,2410,2411,2412,2413,2414,2415,2416,2417,2418,2419,2420,2421,2422,2423,2424,2425,2426,2427,2428,2429,2430,2431,2432,2433,2434,2435,2436,2437,2438,2439,2440,2441,2442,2443,2444,2445,2446,2447,2448,2449,2450,2451,2452,2453,2454,2455,2456,2457,2458,2459,2460,2461,2462,2463,2464,2465,2466,2467,2468,2469,2470,2471,2472,2473,2474,2475,2476,2477,2478,2479,2480,2481,2482,2483,2484,2485,2486,2487,2488,2489,2490,2491,2492,2493,2494,2495,2496,2497,2498,2499,2500,2501,2502,2503,2504,2505,2506,2507,2508,2509,2510,2511,2512,2513,2514,2515,2516,2517,2518,2519,2520,2521,2522,2523,2524,2525,2526,2527,2528,2529,2530,2531,2532,2533,2534,2535,2536,2537,2538,2539,2540,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2551,2552,2553,2554,2555,2556,2557,2558,2559,2560,2561,2562,2563,2564,2565,2566,2567,2568,2569,2570,2571,2572,2573,2574,2575,2576,2577,2578,2579,2580,2581,2582,2583,2584,2585,2586,2587,2588,2589,2590,2591,2592,2593,2594,2595,2596,2597,2598,2599,2600,2601,2602,2603,2604,2605,2606,2607,2608,2609,2610,2611,2612,2613,2614,2615,2616,2617,2618,2619,2620,2621,2622,2623,2624,2625,2626,2627,2628,2629,2630,2631,2632,2633,2634,2635,2636,2637,2638,2639,2640,2641,2642,2643,2644,2645,2646,2647,2648,2649,2650,2651,2652,2653,2654,2655,2656,2657,2658,2659,2660,2661,2662,2663,2664,2665,2666,2667,2668,2669,2670,2671,2672,2673,2674,2675,2676,2677,2678,2679,2680,2681,2682,2683,2684,2685,2686,2687,2688,2689,2690,2691,2692,2693,2694,2695,2696,2697,2698,2699,2700,2701,2702,2703,2704,2705,2706,2707,2708,2709,2710,2711,2712,2713,2714,2715,2716,2717,2718,2719,2720,2721,2722,2723,2724,2725,2726,2727,2728,2729,2730,2731,2732,2733,2734,2735,2736,2737,2738,2739,2740,2741,2742,2743,2744,2745,2746,2747,2748,2749,2750,2751,2752,2753,2754,2755,2756,2757,2758,2759,2760,2761,2762,2763,2764,2765,2766,2767,2768,2769,2770,2771,2772,2773,2774,2775,2776,2777,2778,2779,2780,2781,2782,2783,2784,2785,2786,2787,2788,2789,2790,2791,2792,2793,2794,2795,2796,2797,2798,2799,2800,2801,2802,2803,2804,2805,2806,2807,2808,2809,2810,2811,2812,2813,2814,2815,2816,2817,2818,2819,2820,2821,2822,2823,2824,2825,2826,2827,2828,2829,2830,2831,2832,2833,2834,2835,2836,2837,2838,2839,2840,2841,2842,2843,2844,2845,2846,2847,2848,2849,2850,2851,2852,2853,2854,2855,2856,2857,2858,2859,2860,2861,2862,2863,2864,2865,2866,2867,2868,2869,2870,2871,2872,2873,2874,2875,2876,2877,2878,2879,2880,2881,2882,2883,2884,2885,2886,2887,2888,2889,2890,2891,2892,2893,2894,2895,2896,2897,2898,2899,2900,2901,2902,2903,2904,2905,2906,2907,2908,2909,2910,2911,2912,2913,2914,2915,2916,2917,2918,2919,2920,2921,2922,2923,2924,2925,2926,2927,2928,2929,2930,2931,2932,2933,2934,2935,2936,2937,2938,2939,2940,2941,2942,2943,2944,2945,2946,2947,2948,2949,2950,2951,2952,2953,2954,2955,2956,2957,2958,2959,2960,2961,2962,2963,2964,2965,2966,2967,2968,2969,2970,2971,2972,2973,2974,2975,2976,2977,2978,2979,2980,2981,2982,2983,2984,2985,2986,2987,2988,2989,2990,2991,2992,2993,2994,2995,2996,2997,2998,2999,3000,3001,3002,3003,3004,3005,3006,3007,3008,3009,3010,3011,3012,3013,3014,3015,3016,3017,3018,3019,3020,3021,3022,3023,3024,3025,3026,3027,3028,3029,3030,3031,3032,3033,3034,3035,3036,3037,3038,3039,3040,3041,3042,3043,3044,3045,3046,3047,3048,3049,3050,3051,3052,3053,3054,3055,3056,3057,3058,3059,3060,3061,3062,3063,3064,3065,3066,3067,3068,3069,3070,3071,3072,3073,3074,3075,3076,3077,3078,3079,3080,3081,3082,3083,3084,3085,3086,3087,3088,3089,3090,3091,3092,3093,3094,3095,3096,3097,3098,3099,3100,3101,3102,3103,3104,3105,3106,3107,3108,3109,3110,3111,3112,3113,3114,3115,3116,3117,3118,3119,3120,3121,3122,3123,3124,3125,3126,3127,3128,3129,3130,3131,3132,3133,3134,3135,3136,3137,3138,3139,3140,3141,3142,3143,3144,3145,3146,3147,3148,3149,3150,3151,3152,3153,3154,3155,3156,3157,3158,3159,3160,3161,3162,3163,3164,3165,3166,3167,3168,3169,3170,3171,3172,3173,3174,3175,3176,3177,3178,3179,3180,3181,3182,3183,3184,3185,3186,3187,3188,3189,3190,3191,3192,3193,3194,3195,3196,3197,3198,3199,3200,3201,3202,3203,3204,3205,3206,3207,3208,3209,3210,3211,3212,3213,3214,3215,3216,3217,3218,3219,3220,3221,3222,3223,3224,3225,3226,3227,3228,3229,3230,3231,3232,3233,3234,3235,3236,3237,3238,3239,3240,3241,3242,3243,3244,3245,3246,3247,3248,3249,3250,3251,3252,3253,3254,3255,3256,3257,3258,3259,3260,3261,3262,3263,3264,3265);
    GO
    CREATE PARTITION SCHEME PS
    AS PARTITION PF
    ALL TO ([PRIMARY])
    GO
    CREATE TABLE dbo.Test
    (
        PartCol integer NOT NULL,
        ColB    integer NOT NULL,
        ColC    integer NOT NULL,
        Padding char(120) NOT NULL
    
        PRIMARY KEY CLUSTERED (PartCol, ColB, ColC)
        ON PS(PartCol)
    );
    GO
    CREATE TABLE dbo.PKeys
    (
        PartCol integer NOT NULL,
        ColB    integer NOT NULL,
        ColC    integer NOT NULL,
        
        PRIMARY KEY CLUSTERED (PartCol, ColB, ColC)
        ON [PRIMARY]
    );
    GO
    -- Pretend there are 4,000,000 rows
    UPDATE STATISTICS dbo.Test WITH ROWCOUNT = 400000000, PAGECOUNT = 4000000;
    GO
    -- Pretend there are 200,000 rows
    UPDATE STATISTICS dbo.PKeys WITH ROWCOUNT = 200000, PAGECOUNT = 200;
    GO
    -- Parallel nested loops with dynamic elimination
    -- Uncomment the FORCESEEK hint only if required
    -- on your system.
    SELECT t.*
    FROM dbo.PKeys AS pk
    JOIN dbo.Test AS t /*WITH (FORCESEEK)*/ ON
        t.PartCol = pk.PartCol
        AND t.ColB = pk.ColB
        AND t.ColC = pk.ColC;
    GO
    -- Clean up
    DROP TABLE dbo.Test, dbo.PKeys;
    DROP PARTITION SCHEME PS;
    DROP PARTITION FUNCTION PF;
    
    Query plan:

    Parallel Nested Loops

    • Marked as answer by Iric Wen Tuesday, June 26, 2012 1:43 AM
    Wednesday, June 20, 2012 6:23 PM
  • More generally, it seems that you are looking to use partitioning for performance reasons.  This is generally an error, in my experience.

    Ouch.

    Can you offer a bit of clarification on that  - is it not the intention of Microsoft that partitioning lead to smarter scans and performance improvement, or have they just not quite got there yet?

    FWIW on my project last year I finally did start trying index hints and probably FORCESEEK, when the plans refused to follow what I thought the optimal path should be.  Never got it to work, though.  It was also a large-scale table, billions of rows.  It's just when the scale gets to be a problem and you can't afford a bad plan, that the automagic analysis starts making mistakes.  I've wondered if there might not be some more generic tools ought to be considered for TSQL for these situations, in particular "minimize time" instead of "minimize cost" to increase parallelism in some cases - had some specific cases at hand at the time.  Hmm, could that be part of the problem Gokhan is seeing?

    Josh

    Wednesday, June 20, 2012 6:51 PM
  • Thank you Paul

    I greatly appreciate the answer, additional information and the suggestion on how to get a better answer by questioning better. We choose to have 3305 partitions since we are getting data from county offices and massaging it and replacing the same county in our database (and all related 100+ tables comes with the data). Some counties have less records maybe like 10K parcels (house, building etc plus all transactions since they were collected, mortgage, deed, etc) and some counties are larger 25mil+. To merge that much data at once I don't think is possible instead of partition switches. But now, statistics are not usable, query plans generated are off optimal, it's very challenging. But again how else to meet the requirements I do not know.


    Gokhan Varol

    Wednesday, June 20, 2012 7:44 PM
  • and yes, I just tested the forceseek does the same job.

    Thank again


    Gokhan Varol

    Wednesday, June 20, 2012 7:59 PM